Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Principles of Finance

8.5 Equal Payments with a Financial Calculator and Excel

Principles of Finance8.5 Equal Payments with a Financial Calculator and Excel

Menu
Table of contents
  1. Preface
  2. 1 Introduction to Finance
    1. Why It Matters
    2. 1.1 What Is Finance?
    3. 1.2 The Role of Finance in an Organization
    4. 1.3 Importance of Data and Technology
    5. 1.4 Careers in Finance
    6. 1.5 Markets and Participants
    7. 1.6 Microeconomic and Macroeconomic Matters
    8. 1.7 Financial Instruments
    9. 1.8 Concepts of Time and Value
    10. Summary
    11. Key Terms
    12. Multiple Choice
    13. Review Questions
    14. Video Activity
  3. 2 Corporate Structure and Governance
    1. Why It Matters
    2. 2.1 Business Structures
    3. 2.2 Relationship between Shareholders and Company Management
    4. 2.3 Role of the Board of Directors
    5. 2.4 Agency Issues: Shareholders and Corporate Boards
    6. 2.5 Interacting with Investors, Intermediaries, and Other Market Participants
    7. 2.6 Companies in Domestic and Global Markets
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Video Activity
  4. 3 Economic Foundations: Money and Rates
    1. Why It Matters
    2. 3.1 Microeconomics
    3. 3.2 Macroeconomics
    4. 3.3 Business Cycles and Economic Activity
    5. 3.4 Interest Rates
    6. 3.5 Foreign Exchange Rates
    7. 3.6 Sources and Characteristics of Economic Data
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  5. 4 Accrual Accounting Process
    1. Why It Matters
    2. 4.1 Cash versus Accrual Accounting
    3. 4.2 Economic Basis for Accrual Accounting
    4. 4.3 How Does a Company Recognize a Sale and an Expense?
    5. 4.4 When Should a Company Capitalize or Expense an Item?
    6. 4.5 What Is “Profit” versus “Loss” for the Company?
    7. Summary
    8. Key Terms
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  6. 5 Financial Statements
    1. Why It Matters
    2. 5.1 The Income Statement
    3. 5.2 The Balance Sheet
    4. 5.3 The Relationship between the Balance Sheet and the Income Statement
    5. 5.4 The Statement of Owner’s Equity
    6. 5.5 The Statement of Cash Flows
    7. 5.6 Operating Cash Flow and Free Cash Flow to the Firm (FCFF)
    8. 5.7 Common-Size Statements
    9. 5.8 Reporting Financial Activity
    10. Summary
    11. Key Terms
    12. CFA Institute
    13. Multiple Choice
    14. Review Questions
    15. Problems
    16. Video Activity
  7. 6 Measures of Financial Health
    1. Why It Matters
    2. 6.1 Ratios: Condensing Information into Smaller Pieces
    3. 6.2 Operating Efficiency Ratios
    4. 6.3 Liquidity Ratios
    5. 6.4 Solvency Ratios
    6. 6.5 Market Value Ratios
    7. 6.6 Profitability Ratios and the DuPont Method
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  8. 7 Time Value of Money I: Single Payment Value
    1. Why It Matters
    2. 7.1 Now versus Later Concepts
    3. 7.2 Time Value of Money (TVM) Basics
    4. 7.3 Methods for Solving Time Value of Money Problems
    5. 7.4 Applications of TVM in Finance
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  9. 8 Time Value of Money II: Equal Multiple Payments
    1. Why It Matters
    2. 8.1 Perpetuities
    3. 8.2 Annuities
    4. 8.3 Loan Amortization
    5. 8.4 Stated versus Effective Rates
    6. 8.5 Equal Payments with a Financial Calculator and Excel
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Problems
    12. Video Activity
  10. 9 Time Value of Money III: Unequal Multiple Payment Values
    1. Why It Matters
    2. 9.1 Timing of Cash Flows
    3. 9.2 Unequal Payments Using a Financial Calculator or Microsoft Excel
    4. Summary
    5. Key Terms
    6. CFA Institute
    7. Multiple Choice
    8. Review Questions
    9. Problems
    10. Video Activity
  11. 10 Bonds and Bond Valuation
    1. Why It Matters
    2. 10.1 Characteristics of Bonds
    3. 10.2 Bond Valuation
    4. 10.3 Using the Yield Curve
    5. 10.4 Risks of Interest Rates and Default
    6. 10.5 Using Spreadsheets to Solve Bond Problems
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  12. 11 Stocks and Stock Valuation
    1. Why It Matters
    2. 11.1 Multiple Approaches to Stock Valuation
    3. 11.2 Dividend Discount Models (DDMs)
    4. 11.3 Discounted Cash Flow (DCF) Model
    5. 11.4 Preferred Stock
    6. 11.5 Efficient Markets
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  13. 12 Historical Performance of US Markets
    1. Why It Matters
    2. 12.1 Overview of US Financial Markets
    3. 12.2 Historical Picture of Inflation
    4. 12.3 Historical Picture of Returns to Bonds
    5. 12.4 Historical Picture of Returns to Stocks
    6. Summary
    7. Key Terms
    8. Multiple Choice
    9. Review Questions
    10. Video Activity
  14. 13 Statistical Analysis in Finance
    1. Why It Matters
    2. 13.1 Measures of Center
    3. 13.2 Measures of Spread
    4. 13.3 Measures of Position
    5. 13.4 Statistical Distributions
    6. 13.5 Probability Distributions
    7. 13.6 Data Visualization and Graphical Displays
    8. 13.7 The R Statistical Analysis Tool
    9. Summary
    10. Key Terms
    11. CFA Institute
    12. Multiple Choice
    13. Review Questions
    14. Problems
    15. Video Activity
  15. 14 Regression Analysis in Finance
    1. Why It Matters
    2. 14.1 Correlation Analysis
    3. 14.2 Linear Regression Analysis
    4. 14.3 Best-Fit Linear Model
    5. 14.4 Regression Applications in Finance
    6. 14.5 Predictions and Prediction Intervals
    7. 14.6 Use of R Statistical Analysis Tool for Regression Analysis
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  16. 15 How to Think about Investing
    1. Why It Matters
    2. 15.1 Risk and Return to an Individual Asset
    3. 15.2 Risk and Return to Multiple Assets
    4. 15.3 The Capital Asset Pricing Model (CAPM)
    5. 15.4 Applications in Performance Measurement
    6. 15.5 Using Excel to Make Investment Decisions
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  17. 16 How Companies Think about Investing
    1. Why It Matters
    2. 16.1 Payback Period Method
    3. 16.2 Net Present Value (NPV) Method
    4. 16.3 Internal Rate of Return (IRR) Method
    5. 16.4 Alternative Methods
    6. 16.5 Choosing between Projects
    7. 16.6 Using Excel to Make Company Investment Decisions
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  18. 17 How Firms Raise Capital
    1. Why It Matters
    2. 17.1 The Concept of Capital Structure
    3. 17.2 The Costs of Debt and Equity Capital
    4. 17.3 Calculating the Weighted Average Cost of Capital
    5. 17.4 Capital Structure Choices
    6. 17.5 Optimal Capital Structure
    7. 17.6 Alternative Sources of Funds
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  19. 18 Financial Forecasting
    1. Why It Matters
    2. 18.1 The Importance of Forecasting
    3. 18.2 Forecasting Sales
    4. 18.3 Pro Forma Financials
    5. 18.4 Generating the Complete Forecast
    6. 18.5 Forecasting Cash Flow and Assessing the Value of Growth
    7. 18.6 Using Excel to Create the Long-Term Forecast
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  20. 19 The Importance of Trade Credit and Working Capital in Planning
    1. Why It Matters
    2. 19.1 What Is Working Capital?
    3. 19.2 What Is Trade Credit?
    4. 19.3 Cash Management
    5. 19.4 Receivables Management
    6. 19.5 Inventory Management
    7. 19.6 Using Excel to Create the Short-Term Plan
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Video Activity
  21. 20 Risk Management and the Financial Manager
    1. Why It Matters
    2. 20.1 The Importance of Risk Management
    3. 20.2 Commodity Price Risk
    4. 20.3 Exchange Rates and Risk
    5. 20.4 Interest Rate Risk
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  22. Index

Learning Outcomes

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

  • Use a financial calculator and Excel to solve perpetuity problems.
  • Use a financial calculator and Excel to solve annuity problems.
  • Calculate an effective rate of interest.
  • Schedule the amortization of a loan repayment.

Solving Time Value of Money Problems Using a Financial Calculator

Since the 1980s, many convenient and inexpensive tools have become available to simplify business and personal calculations, including personal computers with financial applications and handheld/desktop or online calculators with many of the functions we’ve studied already. This section will explore examples of both, beginning with financial calculators. While understanding and mastery of the use of time value of money equations are part of a solid foundation in the study of business and personal finance, calculators are rapid and efficient.

We’ll begin with the constant perpetuity that we used to illustrate the constant perpetuity formula. A share of preferred stock of Shaw Inc., pays an annual $2.00 dividend, and the required rate of return that investors in this stock expect is 7%. The simple technique to solve this problem using the calculator is shown in Table 8.7.

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Enter formula 2 ÷ 7 % =   28.57
Table 8.7 Calculator Steps to Find the Required Rate of Return3

Earlier we solved for the present value of a 5-year ordinary annuity of $25,000 earning 8% annually. We then solved for an annuity due, all other facts remaining the same. The two solutions were $99,817.50 and $107,802.50, respectively. We enter our variables as shown in Table 8.8 to solve for an ordinary annuity:

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Enter number of payments 5 N N = 5.00
3 Enter interest rate per payment period 8 I/Y I/Y = 8.00
4 Enter payment amount 25000 +/- PMT PMT = -25,000.00
5 Compute present value CPT PV PV = 99,817.75
Table 8.8 Calculator Steps to Solve for an Ordinary Annuity

Note that the default setting on the financial calculator is END to indicate that payment is made at the end of a period, as in our ordinary annuity. In addition, we follow the payment amount of $25,000 with the +/- keystroke—an optional step to see the final present value result as a positive value.

To perform the same calculation as an annuity due, we can perform the same procedures as above, but with two additional steps after Step 1 to change the default from payments at the end of each period to payments at the beginning of each period (see Table 8.9).

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Change default to payment at end of period 2ND [BGN] 2ND [SET] BGN 0.00
3 Return to calculator mode 2ND [QUIT]   0.00
4 Enter number of payments 5 N N = 5.00
5 Enter interest rate per payment period 8 I/Y I/Y = 8.00
6 Enter payment amount 25000 +/- PMT PMT = -25,000.00
7 Compute present value CPT PV PV = 107,803.17
Table 8.9 Calculator Steps to Solve for an Annuity Due

The procedures to find future values of both ordinary annuities and annuities due are comparable to the two procedures above. We begin with the ordinary annuity, with reminders that this is the default for the financial calculator and that entering the payment as a negative number produces a positive result (see Table 8.10).

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Enter number of payments 5 N N = 5.00
3 Enter interest rate per payment period 4 I/Y I/Y = 4.00
4 Enter payment amount 3000 +/- PMT PMT = -3,000.00
5 Compute future value CPT FV FV = 16,248.97
Table 8.10 Calculator Steps to Find the Future Value of an Ordinary Annuity

Solving for an annuity due with the same details requires the keystrokes listed in Table 8.11.

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Change default to payment at end of period 2ND [BGN] 2ND [SET] BGN 0.00
3 Return to calculator mode 2ND [QUIT]   0.00
4 Enter number of payments 5 N N = 5.00
5 Enter interest rate per payment period 4 I/Y I/Y = 4.00
6 Enter payment amount 3000 +/- PMT PMT = -3,000.00
7 Compute future value CPT FV FV = 16,898.93
Table 8.11 Calculator Steps to Find the Future Value of an Annuity Due

Earlier in the chapter, we explored the effect of interannual compounding on the true cost of money, recalling the basic compounding formula:

(1+i)N(1+i)N

We saw that when modified for monthly compounding at a stated rate of 1.5%, the actual (effective) rate of interest per year was 19.56%. One simple way to prove this is by using the calculator keystrokes listed in Table 8.12.

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Set the display to four decimal places 2ND [FORMAT] 4 ENTER DEC = 4.0000
3 Return to calculator mode 2ND [QUIT]   0.0000
4 Enter (1 + the monthly interest rate) 1.015 YX   1.0150
5 Enter the number of months 12 YX   1.1956
Table 8.12 Calculator Steps to Prove the Actual Rate of Interest per Year

Had we assumed that the stated monthly interest rate of 1.5% could be simply multiplied by 12 months for an annual rate of 18%, we would be ignoring the effect of more frequent compounding. As indicated above, the annual interest on the money that we spent initially, accumulating at a rate of 1.5% per month, is 19.56%, not 18%:

1.1956-$1spentintially=0.1956=19.56%1.1956-$1spentintially=0.1956=19.56%

The final example in this chapter will represent the amortization of a loan. Using a 36-month auto loan for $32,000 at 6% per year compounded monthly, we can easily find the monthly payment and the amortization of this loan on our calculator using the following procedures and keystrokes.

First, we find the monthly payment (see Table 8.13).

Step Description Enter Display
1 Set all variables to defaults 2ND [RESET] ENTER RST 0.00
2 Set payments per year to 12 2ND [P/Y] 12 ENTER P/Y = 12.00
3 Return to calculator mode 2ND [QUIT]   0.00
4 Enter number of payments with the payment multiplier 3 2ND [xP/Y] N N = 36.00
5 Enter annual interest rate 6 I/Y I/Y = 6.00
6 Enter loan amount 32000 PV PV = 32,000.00
7 Compute the monthly payment CPT PMT PMT = -973.50
Table 8.13 Calculator Steps to Find the Monthly Payment of a Loan

We’ve verified the amount of our monthly debt service, including both the interest and repayment of the principal, as $973.50. The next step with our calculator is to verify our amortization at any point (see Table 8.14).

Step Description Enter Display
1 Set previous work as an amortization worksheet 2ND [AMORT] P1 = 1.00
2 Set beginning period to 1 1 ENTER P1 = 1.00
3 Set ending period to 12 ↓ 12 ENTER P2 = 12.00
4 Display amortization data at the end of month 12 BAL = 21,965.02
5   PRN = -10,034.98
6   INT = -1,647.02
Table 8.14 Calculator Steps to Verify Amortization at the End of One Year

Without resetting the calculator, we will try a second example, this time reviewing the second full year of amortization at the end of 24 months (see Table 8.15).

Step Description Enter Display
1 Set previous work as an amortization worksheet 2ND [AMORT] P1 = 1.00
2 Change beginning period to month 13 13 ENTER P1 = 13.00
3 Change ending period to month 24 ↓ 24 ENTER P2 = 24.00
4 Display amortization data at the end of month 24 BAL = 11,311.13
5   PRN = -10,653.89
6   INT = -1,028.11
Table 8.15 Calculator Steps to Verify Amortization at the End of Two Years

Solving Time Value of Money Problems Using Excel

Microsoft’s popular spreadsheet program Excel is arguably one of the most common and powerful numeric and data analysis products available. Yet while mastery of Excel requires extensive study and practice, enough basics can be learned in two or three hours to provide the user with the ability to solve problems quickly and conveniently, including extensive financial capability. Most of the calculations in this chapter were prepared with Excel.

The boxes in the Excel gridwork, known individually as cells (located at the intersection of a column and a row), can contain numbers, text, and very powerful formulas (or functions) for calculations and data analytics. Cells, rows, columns, and groups of cells (ranges) are easily moved, formatted, and replicated. In the mortgage amortization table for 240 months seen in Section 8.3.2, only the formulas for month 1 were typed in. With one simple command, that row of formulas was replicated 239 more times, with each line updating itself with relevant number adjustments automatically. With some practice, a long table such as that can be constructed by even a relatively new user in less than 10 minutes.

In this section, we will illustrate how to use Excel to solve problems from earlier in the chapter, including perpetuities, ordinary annuities, effective interest rates, and loan amortization. We will omit the basic dynamics of an Excel spreadsheet because they were presented sufficiently in preceding chapters.

Revisiting the constant perpetuity from Section 8.1, in which our shares of Shaw Inc., preferred stock pay an annual fixed dividend of $2.00 and the required rate of return is 7%, we do not use an Excel function for this simple operation. The two values are entered in cells B3 and B4, respectively.

We enter a formula in cell B6 to perform the division and display the result in that cell. The actual contents of cell B6 are typed below it for your reference, in cell B8 (see Figure 8.2).

Excel Spreadsheet for Valuing a Perpetuity. It shows the value of an indefinite dividend per share in cell B3 and the required rate of return cell B4. This results in price per share, which is calculated by dividing the indefinite dividend per share by the required rate of return. In Excel, this is written as =B3/B4.
Figure 8.2 Excel Spreadsheet for Valuing a Perpetuity

Download the spreadsheet file containing key Chapter 8 Excel exhibits.

To find the present value of an ordinary annuity, we revisit Section 8.2.1. You will draw $25,000 at the end of each year for five years from a fund earning 8% annually, and you want to know how much you need in that fund today to accomplish this. We accomplish this in Excel easily with the PV function. The format of the PV command is

=PV(rate,periods,payment,0,0)

Only the first three arguments inside the parentheses are used. We’ll place them in cells and refer to those cells in our PV function. As an option, you could also type the numbers into the parentheses directly. Notice the slight rounding error because of decimal expansion. Also, the payment must be entered as a negative number for your result to be positive; this can be accomplished either by making the $25,000 in cell B5 a negative amount or by placing a minus sign in front of the B5 in the formula’s arguments. In cell B3, you must enter the percent either as 0.08 or as 8% (with the percent sign). We repeated the formula syntax and the actual formula inputs in column A near the result, for your reference (see Figure 8.3).

Excel Spreadsheet Showing the Present Value of an Ordinary Annuity. It shows the rate, periods, payments, which gives the present value of the annuity due with a total of $ 99,817.75. The Excel formula used to find the present value or an ordinary annuity is =PV open parenthesis B3 comma B4 comma B5 comma 0 comma 0 close parenthesis.
Figure 8.3 Excel Spreadsheet Showing the Present Value of an Ordinary Annuity

We also found the present value of an annuity due. We use the same information from the ordinary annuity problem above, but you will recall that the first of five payments happens immediately at the start of year 1, not at the end. We follow the same procedures and inputs as in the previous example, but with one change to the PV function: the last argument in the parentheses will change from 0 to 1. This is a toggle switch that commands the PV function to treat this as an annuity due instead of an ordinary annuity (see Figure 8.4).

Excel Spreadsheet Showing the Present Value of an Annuity Due. It shows the rate, periods, payments, which gives the present value of the annuity due with a total of $ 107,803.17. The excel formula used to find the present value of an annuity due is =PV open parenthesis B3 comma B4 comma B5 comma 0 comma 1 close parenthesis.
Figure 8.4 Excel Spreadsheet Showing the Present Value of an Annuity Due

Section 8.2 introduced us to future values. Comparable to the PV function above, Excel provides the FV function. Using the same information—$3,000 invested annually for five years, starting one year from now, at 4%—we’ll solve using Excel (see Figure 8.5). The format of the command is

=FV(rate,periods,payment,0,0)
Excel Spreadsheet Showing the Future Value of an Ordinary Annuity. It shows the rate, periods, payments, which gives the present value of the annuity due with a total of $ 16,248.97. The excel formula used to calculate the future value of an ordinary annuity is =FV open parenthesis B3 comma B4 comma B5 comma 0 comma 0 close parenthesis.
Figure 8.5 Excel Spreadsheet Showing the Future Value of an Ordinary Annuity

As with present values, using the same data but solving for an annuity due requires the fifth argument inside the parentheses to be changed from 0 to 1; all other values remain the same (see Figure 8.6).

Excel Spreadsheet Showing the Future Value of an Annuity Due. It shows the rate, periods, payments, which gives the future value of the annuity due with a total of $ 16,898.93. The excel formula used to calculate the future value of an annuity due is =FV open parenthesis B3 comma B4 comma B5 comma 0 comma 1 close parenthesis.
Figure 8.6 Excel Spreadsheet Showing the Future Value of an Annuity Due

In Section 8.4, we explained the difference between stated and effective rates of interest to show the true cost of borrowing, in this case for a one-year period, if interest is compounded for periods within a year. The syntax for the Excel effect function to calculate this rate is

=EFFECT(rate,periods)

where rate is the nominal rate and periods represents the number of periods within a year.

Earlier, our example showed that 1.5% compounded monthly results in not 18% per year but actually over 19.56% (see Figure 8.7).

Excel Spreadsheet Showing Effective Interest Rate for annual, quarterly, and monthly periods per year. The nominal interest rate in this example is 18%. With annual interest, there is one period per year and the effective interest is 18%. With quarterly interest, there are 4 periods per year and the effective interest is 19.25%. With monthly interest, there are 12 periods per year and the effective interest is 19.56%.
Figure 8.7 Excel Spreadsheet Showing Effective Interest Rate

Note several things: First, the nominal interest rate is entered as a percent. Second, the actual effect function in C7 is typed as =EFFECT(rate,B7); we use the word rate because we actually assigned a name to cell B3, so Excel can use it in a function and replicate it without it changing. When cell C7 is replicated to C8 and C9, rate remains the same, but the formulas automatically adjust to use B8 and B9 for the periods.

To assign a name to a cell, keep in mind that every cell has column-row coordinates. We want cell B3 to be the anchor of our effective rate calculations. Rather than referring to cell B3, we can name it, and in this case, we use the name rate, which we can then use in formulas like any other Excel cell letter-number reference. Place the cursor in cell B3. Now, look at cell A1 on the grid: right above that cell, you see a box displaying B3, the current cursor location. If you click in that box and type “rate” (without the quotation marks), as we did, then hit the enter key, the value in that box will change to rate. Now, if you type “rate” (again, without quotation marks) into a formula, Excel knows to use the contents of cell B3.

Excel provides convenient tools for figuring out amortization. We’ll revisit our 36-month auto loan for $32,000 at 6% per year, compounded monthly. A loan amortization table for a fixed interest rate debt is usually formatted as follows, with the Interest and Principal columns interchangeable:

Period     Payment     Interest     Principal     Balance

In Excel, a table is completed by using the function PMT. The individual steps follow.

  1. List the information about the loan in the upper left of the worksheet, and create the column headings for the schedule of amortization. Type “B5” (without the quotation marks) in cell E9 to begin the schedule. Then enter 1 for the first month under the Payment # (or Month) column, in cell A10 (see Figure 8.8).
    Amortization table showing the annual rate of interest, years, payments per year, the loan amount. There are headings for Payment number or month, payment, interest, principal, and remaining balance. These will be calculated when formulas are inserted.
    Figure 8.8 Step 1 of Creating an Amortization Table
  2. Next, in cell B10, the payment is derived from the formula =PMT(rate,periods,pv), with PV representing the present value, or the loan amount. Because we are compounding monthly, enter C$2 and C$3 for the rate and periods, respectively. Cell B5 is used for the loan amount, but notice the optional minus sign placed in front of the entry B$5; this causes the results in the schedule to be displayed as positive numbers. The dollar sign ($) inserted in the cell references forces Excel to “freeze” those locations so that they don’t attempt to update when we replicate them later; this is known in spreadsheet programs as an absolute reference (see Figure 8.9).
    Amortization table showing the annual rate of interest, years, payments per year, the loan amount, and remaining balance. The formula to calculate the monthly payment is =PMT open parenthesis C dollar sign 2 comma C dollar sign 3 comma minus sign B dollar sign 5 close parenthesis.
    Figure 8.9 Step 2 of Creating an Amortization Table
  3. The next step is to calculate the interest. We take the remaining balance from the previous line, in this case cell E9, and multiply it by the monthly interest rate in cell C2, typing C$2 to lock in the reference. The remaining balance of the loan should always be multiplied by this monthly percentage (see Figure 8.10).
    Amortization table showing the annual rate of interest, years, payments per year, the loan amount, and the total remaining balance. The excel formula used to calculate the amount of interest paid in the first pay period is =E9 * C dollar sign 2.
    Figure 8.10 Step 3 of Creating an Amortization Table
  4. Because this is a fixed-rate loan, whatever is left from each payment after first deducting the interest represents principal, the amount by which the balance of the outstanding loan balance is reduced. Therefore, the contents of cell D10 represent B10, the total payment, minus C10, the interest portion (see Figure 8.11). No dollar signs are included because this cell reference can adjust to each row into which this formula is replicated, as will be seen in the following examples.
    Amortization table showing the annual rate of interest, years, payments per year, the loan amount, and total remaining balance. The excel formula used to find the principal payment is =B10 minus C10.
    Figure 8.11 Step 4 of Creating an Amortization Table
  5. Because our principal portion of the last payment has reduced our outstanding balance, it is subtracted from the preceding balance in cell E9 (see Figure 8.12). The command therefore is =E9-D10.
    Amortization table showing the annual rate of interest, years, payments per year, the loan amount, and total remaining balance. To determine the remaining balance, the principal paid is subtracted from the remaining balance. The excel formula for this is =E9 minus D10.
    Figure 8.12 Step 5 of Creating an Amortization Table

Now that the first full row is defined, an amortization schedule is easily developed by Excel’s replication abilities. Place the cursor on cell A10, hold down the left mouse button, and drag the cursor to cell E10. Cells A10 through E10 in row 10 should now be highlighted. Release the mouse button. Then “grab” the tiny square symbol at the bottom right of cell E10 and drag it downward as far as you need; in this case, you’ll need 35 more rows because this is a 36-month loan, so it will end at row 45. We added a line for totals.

This is now a complete loan amortization schedule (see Figure 8.13). The first several periods display, followed by the last few periods, to prove that the schedule is complete (data rows for month 4 to month 22 are hidden).

Amortization table showing the annual rate of interest, years, payments per year, the loan amount, and total remaining balance. The payment, interest, and principle are shown for each payment. In each row, the principal paid is deducted from the remaining balance to identify the new remaining balance amount. This process carries on till the remaining balance is reduced to zero and the amortization table is complete.
Figure 8.13 Completed Amortization Schedule

This will look familiar; it’s the same amortization table used as a proof in Section 8.3 (see Table 8.4). There is no rounding error because Excel uses the full decimal expansion in its calculations.

This chapter has explored the time value of money by expanding on the concepts discussed in Time Value of Money I with additional funds being periodically added to or subtracted from our investment, either compounding or discounting them according to the situation. In all cases, the payments in the stream were identical. If they had not been identical, a separate set of operators would be required, and these will be addressed in the next chapter.

Footnotes

  • 3The specific financial calculator in these examples is the Texas Instruments BA II PlusTM Professional model, but you can use other financial calculators for these types of calculations.
Do you know how you learn best?
Kinetic by OpenStax offers access to innovative study tools designed to help you maximize your learning potential.
Order a print copy

As an Amazon Associate we earn from qualifying purchases.

Citation/Attribution

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/principles-finance/pages/1-why-it-matters
  • 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/principles-finance/pages/1-why-it-matters
Citation information

© May 20, 2022 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.