Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

12.2 Financial Functions in Microsoft Excel

Workplace Software and Skills12.2 Financial Functions in Microsoft Excel

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Learning Objectives

By the end of this section, you will be able to:

  • Describe the use of financial functions
  • Use common financial functions in Excel

WorldCorp and all other corporations all over the world use financial equations to manage their businesses. These equations can help business leaders understand the impact of taking out a loan to purchase equipment, the value of a particular financial investment, and other related decisions. Microsoft Excel has programmed many of the more common financial equations as functions so that you do not need to type a formula. Instead, you use the name of the function and the variables. This means that if you want to calculate the investment of a stock or bond, you may need to have three of the five financial variables in the time value of money theory. Similar to a financial calculator, you can use FV, PV, PMT, N, or I to figure out if an investment is worthwhile. You have already been briefly introduced to some of these functions in The Advantages of a Data Table.

In this section, you will learn how to use financial functions in Excel in the context of the time value of money theory and depreciation. These are common accounting principles that will be briefly discussed. More information on these concepts can be found in a standard accounting textbook.

Real-World Application

Add-ins

Excel comes with standard financial functions; however, many users are able to “add-in” other functions and utilities. Of course, the common user will find the preexisting functions more than sufficient for most of their needs; however, if you have any financial responsibilities at an organization, you may find a need for these additional functions or features to help speed up your work. You can find preset add-ins on the Insert tab under either My Add-ins for existing ones that you may have installed already or Get Add-ins for others available through Office or from other companies. You will find an interesting list of choices when searching for add-ins, such as data analytics, productivity, and financial connector. Under Financial Connector, some of the add-ins available include a stock connector, as well as additional financial analysis and financial analytics tools. These can serve as powerful features to further automate and keep current financial information you are using to populate your sheets.

Applications of Financial Functions

Financial functions are used for calculating a diverse set of common key performance indicators in a business, or for predicting the value of money through time. The time value of money theory states that money loses value as time passes. It is a simple concept that is ingrained in every business decision because it is important for a business to use financial instruments and investments to increase the value of their savings or idle cash. One way you can see this theory in practice is in the steady increase in the sales prices of goods over time.

Common Financial Functions in Practice

You were introduced to the Excel function PMT in The Advantages of a Data Table for determining the monthly (or yearly, daily, or quarterly) payment of an investment or loan. There are other important variables involved in the financial functions related to the time value of money theory, which can help a business determine profitability. This section will focus on an annual payment rather than a monthly payment. But keep in mind, many loans require monthly payments and the variables in the function will need to be adjusted accordingly.

There are five main time value of money variables (PMT, RATE, NPER, PV, and FV) used in these calculations. Table 12.1 lists these variables and what they represent in Excel. To find the answer to a time value of money problem, you must have the values of at least three of the five variables. There is an additional variable that you need to consider and that is “Type.” In the functions, “Type” represents when payments are made. If they are made at the beginning of the term, use 1 in the function. If you do not enter anything, Excel will use the default of “0,” meaning payments are made at the end of the term. The timing of the payments impacts the amount of interest paid.

One way to calculate any of these time value of money variables is to use a financial calculator, which works like a regular calculator, except that it has certain financial functions programmed into it. A financial calculator has a button associated with each of the time value of money variables. Excel also has these functions programmed into it.

Variable Definition
PMT The payment of an investment or loan per period (usually monthly)
RATE The interest rate on an investment, loan, or bond yield
NPER The number of periods in a calculation (usually years, quarters, or months)
PV The initial investment of a business or security purchase
FV The monetary value of a business or security investment at the end of all periods
Type Whether payments are made at the beginning of the term or at the end of the term; the default is “0” or at the end of the term
Table 12.1 Time Value of Money Variables

PMT

To calculate PMT, you need at least three of the other variables. For this example, assume that WorldCorp is building a new manufacturing plant, at a cost of $4,743,542. To finance the investment, they will borrow the money from a bank, rather than use their savings. The bank terms are 5.6 percent for a twelve-year loan. You will use these variables to find the amount WorldCorp will pay each year for a loan. You know the loan term (NPER, which is the number of periods for the loan), the interest rate (RATE), and the original amount of the loan (PV). You do not know the future value (FV) or the yearly payment installment (PMT).

Excel performs these time value of money calculations using functions and the values of the known variables. First, construct a table that lists the five variables and the values you have. You can then use the Excel function to calculate PMT (Figure 12.2). Recall that this function can be found on the Formulas tab under Financial in the Function Library command group. You enter all of the values into the dialog box or in the Formula Bar using the cell references. Figure 12.2 shows the PMT function using the variables and the cell references.

Formulas tab selected. Function Arguments pane displays PMT options: Rate (D2 = 0.056), Nper (D1 = 12), Pv (D3 = 4743542), Formula result = -553454.1521. Cell D3 selected ($4,743,542.00).
Figure 12.2 If you do not know the installment, but you know the periods, the interest, and the principal, you can calculate the installment. Excel will use the default of “0” for type. You do not need to enter anything for FV. (Used with permission from Microsoft)

Figure 12.3a shows the yearly installment WorldCorp will have to pay on this loan. As was noted in The Advantages of a Data Table, Excel gives the PMT answer as a negative number by default. You can change this by putting a “-” sign in front of the PV cell reference or after the “=” in the formula in the Formula Bar (Figure 12.3b).

(a) Formulas tab selected. Cell D3 displays $553,454.15 in red font. Formula bar reads: =PMT(D2,D1,D3). (b) Formula bar reads: =PMT(D2,D1,-D3). Cell D3 in black font.
Figure 12.3 (a) Notice the final answer is negative (as indicated by “()” and red font. (b) The “-” in front of D3 changes the installment payment to a positive number. (Used with permission from Microsoft)

Mac Tip

In the Mac version of Excel, using the functions for all of the time value of money variables does not open a dialog box, as it does in the Windows version. Instead, you must enter the values between the parentheses in the Formula Bar.

RATE

The RATE function works in the same way as the PMT function. Again, you can find the RATE function in the financial functions library. For this example, WorldCorp wants to open another manufacturing plant for making headphones. The cost of the site and the machinery is $3,843,974, but they will use a down payment of $450,000. The loan they would need then would be $3,393,974 (total cost − $450,000). The bank loan will be over twelve years, with yearly payments of $427,308.08. You need to determine the interest rate. Figure 12.4 shows the table of variables, but this time the interest rate is missing. You can then use the RATE function to determine the interest rate (Figure 12.5).

Function Arguments pane displays RATE options: Nper (D1 =12), Pmt (-D6 = -427308.08), Pv (D5 = 3393974), Fv, Type. Formula result = -7.00% is in the left corner. Spreadsheet visible in background.
Figure 12.4 You can find the interest rate if you have the loan term, the loan amount, and the payment. Recall that you need at least three of the variables and be sure to enter a “-” in front of the PMT. (Used with permission from Microsoft)
An arrow points to cell D2 where 7.00% is visible and =RATE(D1,-D6,D5) is in the formula bar.
Figure 12.5 The function returns the interest rate based on the other variable values. (Used with permission from Microsoft)

NPER

You can use the NPER function in Excel in the same way as the other time value of money functions to determine the amount of time required to repay the loan. For this example, WorldCorp has decided to change the look of their retail stores. They asked you, a financial analyst, to find the best bank loan terms—the one that costs the company the least amount in total payment. The average cost of each store renovation is $64,757. Bank X offers an interest rate of 5.45 percent and Bank Y offers an interest rate of 6.55 percent. Bank Y is your current bank, and you have a great working relationship with the employees. You want to keep banking there, but want to understand the long-term impact of the higher interest rate for this loan. You have budgeted $1,500 each month toward the loan for an annual payment of $18,000 ($1,500 × 12 months). You can use those values in the NPER function Figure 12.6 to determine the repayment time period for each loan Figure 12.7, which you can then use to determine the total payment of the loan. You would multiply the PMT by the NPER you just found.

Function Arguments pane displays NPER options: Rate (D3 = 0.0545), Pmt (-D6 = -18000), Pv (D4 =64757), Fv, Type. Formula result = 4.112612058 is in the left corner. Spreadsheet visible in background.
Figure 12.6 Bank X and Bank Y offer different interest rates. Use the NPER function to find the number of payment periods for Bank X. (Used with permission from Microsoft)
An arrow points to cell D2 listing the NPER (Years) as 4.11 for Bank X. An arrow points to cell D11 listing the NPER (Years) as 4.24 for Bank Y.
Figure 12.7 Repeat the NPER function for Bank Y. (Used with permission from Microsoft)

The difference between the two options might not seem that significant: 4.11 years compared with 4.24 years. However, when you take that number and multiply it by the yearly installment, you can see the overall difference between the two loans. For Bank X, you will pay a total of $74,027.02 (= 4.11 × $1,800) and using the same process for Bank Y, the total cost is $76,239.91. It would save you more than $2,000 by going with Bank X. You will need to decide if that is enough savings to take the business away from your current bank. This example shows you that sometimes decisions are not always based on the numbers. Sometimes nonfinancial aspects must be considered when making decisions such as this.

Mac Tip

The Mac version requires the periods to be in months.

PV

Excel’s PV function allows you to calculate the principal of a loan. Suppose you are WorldCorp’s new financial analyst, and your supervisor asked you to track down all of the company’s outstanding bank loans and their original principals. The first loan you find has a monthly payment of $8,673.38, a period of eight years and an annual interest rate of 6.55 percent. As with other examples, the first step is to set up your table of information. However, the table you have been using has the term set up in years, but the term of this loan is in months. You will need to multiply 8 years by 12 months to get NPER, and you will need to divide your annual interest (RATE) by 12 months (Figure 12.8) to get the monthly interest. Now that you have converted all of your figures to months, you can use the PV function from Excel to get the original principal of $646,743.00 (Figure 12.9).

Function Arguments pane displays PV options: Rate (J2 = 0.005458333), Nper (J1 = 96), Pmt (-J4 = -8673.380198), Fv, Type. Formula result = $646,743.00 is in the left corner. Spreadsheet visible in background.
Figure 12.8 The financial functions in Excel are based on yearly information. The Excel function PV calculates the principal of a loan, but you need to convert yearly information to monthly information. (Used with permission from Microsoft)
In the spreadsheet, cell J3 is selected and =PV(J2,J1,-J4) is in the formula bar. An arrow points to the PV / Principal data in cell J3, $646,743.00.
Figure 12.9 PV gives you the principal amount of the loan based on the interest rate and repayment period. (Used with permission from Microsoft)

FV

The final time value of money function you will use is for the future value, which the previous examples have not used. For this example, you will analyze a stock investment. As a financial analyst at WorldCorp, you need to assess the value of investments in a savings account to determine whether it would be better for WorldCorp to use this cash for buying stock of other companies to get a greater return. The stocks they are considering will increase in market value in the future, so you will need to calculate the amount WorldCorp will gain when they sell it. They have $5,456,254.00 in their savings account now. They are considering investing that savings in other company stocks that increase in value on average 6.57 percent per year, and they would plan to hold on to the stock for nine years. You would use the FV function, as shown in Figure 12.10 to determine this value. You can then compare that with what they would get if they just keep the funds in the savings account during that nine-year time period (Figure 12.11).

Function Arguments pane displays FV options: Rate (D2 = 0.0657), Nper (D1 = 9), Pmt, Pv (-D3 = -5456254), Type. Formula result = $9,674,071.02 is in left corner. Spreadsheet visible in background.
Figure 12.10 In this example, there is no annual payment you are making. Calculating the future value uses the FV function and three of the other four time value of money variables. Excel assumes PMT is “0” if no value is entered. (Used with permission from Microsoft)
Cell D5 lists $9,674,071.02 in (Value when sold). An arrow points to cell D5 (selected) and =FV(D2,D1,,-D3) is visible in the formula bar.
Figure 12.11 Again, like the other functions, the result will be negative if you do not add the “-” before the investment amount. (Used with permission from Microsoft)

Depreciation

Depreciation is not one of the five variables used in time value of money theory, but it is an important function used in accounting. The steady decrease in value of an asset, such as a piece of equipment, over time is called depreciation. The IRS lets businesses calculate depreciation as an expenditure, which in turn reduces the total amount they are taxed each year. There are several different depreciation functions in Excel, but this section will focus on the DB (declining balance) function. This is one of the most used methods of depreciation in accounting. With this function, you are essentially reducing the total value of the asset over a period of time.

The formula for depreciation takes into account the original price, the current market value of the item (or the salvage value), the years of use (or useful life), and the period. The years of use is the average lifespan of the machine. The period is the time that has passed since the purchase. The other variable in the function is Month. This can be used if the machine was purchased in the middle of the year. Excel will assume a normal twelve-month year for the first year if you leave this value blank. The number you enter here is the number of months the business owned the asset during a partial year. For this example, you will use the values in Figure 12.12, which shows the DB function. Figure 12.13 shows the depreciation of the asset. As shown, during period 2, the machine is $18,627.12 less than its original value, meaning that in year 2 the machine is worth $61,372.88 (or $80,000 − $18,627.12).

Function Arguments pane displays DB options: Cost (B1 = 80000), Salvage (B2 = 8000), Life (B3 = 5), Period (B4 = 2), Month. Formula result = $18,627.12. Spreadsheet visible in background.
Figure 12.12 The salvage value is the worth of the asset if you were to sell it. The DB function calculates depreciation, which can reduce a company’s taxes. (Used with permission from Microsoft)
An arrow points to $18,627.12 in cell B5 (Depreciation).
Figure 12.13 The resulting value is the annual depreciation of the asset to be deducted as an expense. (Used with permission from Microsoft)

Spotlight on Ethics

Procedures and Reporting Standards

Financial accounting follows a set of standardized rules. All publicly traded corporations use Generally Accepted Accounting Procedures (GAAP) or International Financial Reporting Standards (IFRS) standards. There are options within GAAP that let the company report some accounts differently. For example, inventory can be valued as first in, first out (FIFO) or last in, first out (LIFO). It is important for a company to disclose all these optional changes to the GAAP in the financial statement notes, and in the annual report. For example, a company can explain how they calculated depreciation or value of inventory. Auditors use this information to ensure that the company has followed the appropriate accounting practices and that the financial statements are an accurate reflection of the company’s financial health.

These notes also allow potential investors to consider them in an analysis of a company and in comparing the company with another company in the same industry. Without including full financial statement notes to explain variations in accounting principles, auditors and investors may not be able to determine the accuracy of a company’s financial statement, which could lead to questioning a company’s ethics and their adherence to GAAP or IFRS.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.