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

9.2 Unequal Payments Using a Financial Calculator or Microsoft Excel

Principles of Finance9.2 Unequal Payments Using a Financial Calculator or Microsoft 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:

  • Calculate unequal payments using a financial calculator.
  • Calculate unequal payments using Microsoft Excel.

Using a Financial Calculator

A financial calculator provides utilities to simplify the analysis of uneven mixed cash streams (see Table 9.8).

Earlier, we explored the future value of a seven-year mixed stream, with $2,000 being saved each year, plus an additional $10,000 in year 4 and an additional $3,000 in year 6. All cash flows and balances earn 7% per year compounded annually, and the payments are made at the start of each year. We proved that this result totals approximately $35,062.26. We begin by clearing all memory functions and then entering each cash flow as follows:

Step Description Enter Display
1 Clear cash flow memory CF 2ND [CLR WORK] CF0 0.00
2 Enter 0 for cash flow at Time 0 ENTER CF0 0.00
3 Move to next entry C01 0.00
4 Enter first cash flow 2000 ENTER C01 = 2000.00
5 Move to next entry ↓ ↓ C02 0.00
6 Enter second cash flow 2000 ENTER C02 = 2000.00
7 Move to next entry ↓ ↓ C03 0.00
8 Enter third cash flow 2000 ENTER C03 = 2000.00
9 Move to next entry ↓ ↓ C04 0.00
10 Enter fourth cash flow 12000 ENTER C04 = 12000.00
11 Move to next entry ↓ ↓ C05 0.00
12 Enter fifth cash flow 2000 ENTER C05 = 2000.00
13 Move to next entry ↓ ↓ C06 0.00
14 Enter sixth cash flow 5000 ENTER C06 = 5000.00
15 Move to next entry ↓ ↓ C07 0.00
16 Enter seventh cash flow 2000 ENTER C07 = 2000.00
17 Press NPV NPV I 0.00
18 Enter interest rate 7 ENTER I = 7.00
19 Press down arrow to show current NPV rate NPV 0.00
20 Press CPT to find net present value CPT NPV 20,406.56
Table 9.8 Steps for Calculating Uneven Mixed Cash Flows2

At this point, we have found the net present value of this uneven stream of payments. You will recall, however, that we are not trying to calculate present values; we are looking for future values. The TI BA II Plus™ Professional calculator does not have a similar function for future value. This means that either we can find the future value for each payment in the stream and combine them, or we can take the net present value we just calculated and easily project it forward using the following keystrokes. Note the net present value solution in Step 20 above. We will use that and then use the simpler of the two approaches to calculate future value (see Table 9.9).

Step Description Enter Display
21 Enter NPV from Step 20 20406.56 PV = 20,406.56
22 Enter number of compounding periods 8 N N = 8.00
23 Enter interest rate 7 I/Y I/Y = 7.00
24 Calculate future value CPT FV FV = -35,062.27
Table 9.9 Steps for Calculating Uneven Mixed Cash Flows, Continued

This is consistent with the solution we found earlier, with a difference of one cent due to rounding error.

We may also use the calculator to solve for the present value of a mixed cash stream. Earlier in this chapter, we asked how much money you would need today to fund the following five annual withdrawals, with each withdrawal made at the end of the year, beginning one year from now, and all remaining money earning 6% compounded annually:

Year 1 2 3 4 5
  $17,000 $17,000 $17,000 $17,500 $18,000
Table 9.10

We determined these withdrawals to have a total present value of $72,753.30. Here is an approach to a solution using a financial calculator. In this example, we will store all cash flows in the calculator and perform an operation on them as a whole (see Table 9.11). Because we will use the NPV function (to be explored in more detail in a later chapter), we enter our starting point as 0 because we do not withdraw any cash until one year after we begin.

Step Description Enter Display
1 Clear cash flow memory CF 2ND [CLR WORK] CF0 0.00
2 Enter 0 for cash flow at Time 0 ENTER CF0 0.00
3 Move to next entry C01 0.00
4 Enter first cash flow 17000 ENTER C01 = 17000.00
5 Move to next entry ↓ ↓ C02 0.00
6 Enter second cash flow 17000 ENTER C02 = 17000.00
7 Move to next entry ↓ ↓ C03 0.00
8 Enter third cash flow 17000 ENTER C03 = 17000.00
9 Move to next entry ↓ ↓ C04 0.00
10 Enter fourth cash flow 17500 ENTER C04 = 17500.00
11 Move to next entry ↓ ↓ C05 0.00
12 Enter fifth cash flow 18000 ENTER C05 = 18000.00
13 Press NPV NPV I 0.00
14 Enter interest rate 6 ENTER I = 6
15 Press down arrow to show current NPV rate NPV 0.00
16 Press CPT to find net present value CPT NPV = 72,753.49
Table 9.11 Steps for Calculating the Present Value of a Mixed Cash Stream

This result, you will remember, was calculated earlier in the chapter by the formula approach.

Using Microsoft Excel

Several of the exhibits already in this chapter have been prepared with Microsoft Excel. While full 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 quickly and conveniently solve problems, including extensive financial applications. Potential employers and internship hosts have come to expect basic Excel knowledge, something to which you are exposed in college.

We will demonstrate the same two problems using Excel rather than a calculator:

  1. The future value of a mixed cash stream for a seven-year investment
  2. The present value of a mixed cash stream of five withdrawals that you wish to make from a fund to be established today

Beginning with the future value problem, we created a simple matrix to lay out the mixed stream of future cash flows, starting on the first day of each year, with all funds earning 7% throughout. Our goal is to determine how much money you will have saved at the end of this seven-year period.

Table 9.12 repeats the data from earlier in the chaper for your convenience.

Year 0 1 2 3 4 5 6 7
Cash Invested $0.00 $2,000 $2,000 $2,000 $12,000 $2,000 $5,000 $2,000
Cumulative Cash Flows   $2,000 $4,000 $6,000 $18,000 $20,000 $25,000 $27,000
Years to Compound   7 6 5 4 3 2 1
Table 9.12

Figure 9.2 is an Excel matrix that parallels Table 9.12 above.

A screenshot of excel shows compound interest assuming a 7% rate of interest. The amount invested, interest received on balance, and cumulative cash flow are shown for 7 years. These are added to find the total for each line item at the end of seven years.
Figure 9.2 Compound Interest Example

Download the spreadsheet file containing key Chapter 9 Excel exhibits.

We begin by entering the cash flow as shown in Figure 9.2. The assumed interest rate is 7%. The interest on the balance is calculated as the amount invested at the start of the year multiplied by the assumed interest rate. The cumulative cash flows of each year are calculated as follows: for year 1, the amount invested plus the interest on the balance; for years 2 through 7, the amount invested plus the interest on the balance plus the previous year’s running balance. By adding up the amount invested and the interest on the balance, you should arrive at a total of $35,062.27.

We can use Excel formulas to solve time value of money problems. For example, if we wanted to find the present value of the amount invested at 7% over the seven-year time period, we could use the NPV function in Excel. The dialog box for this function (Rate, Value 1, Value2) is shown in Figure 9.3.

The NPV function in Excel shows drop-down menus where you can enter numerical values for Rate, Value 1, and Value 2.
Figure 9.3 Dialog Box for NPV Function, Problem 1

The function argument Rate is the interest rate; Value1, Value2, and so on are the cash flows; and “Formula result” is the answer.

We can apply the NPV function to our problem as shown in Figure 9.4.

The NPV function in Excel shows the drop-down menu with the Rate cell value as open parenthesis C1 close parenthesis and Value 1 as open parenthesis C3 colon I3 close parenthesis. The formula result is $20,406.56.
Figure 9.4 Applying the NPV Function, Problem 1

Please note that the Rate cell value (C1) and the Value1 cell range (C3:I3) will vary depending on how you set up your spreadsheet.

The non-Excel version of the problem, using an assumed interest rate of 7%, produces the same result.

Year 1 2 3 4 5 6 7
Amount Invested at Start $2,000 $2,000 $2,000 $12,000 $2,000 $5,000 $2,000
NPV $20,406.56            
Table 9.13

We conclude with the second problem addressed earlier in this chapter: finding the present value of an uneven stream of payments. We can use Excel’s NPV function to solve this problem as well (see Figure 9.5).

Year 0 1 2 3 4 5
Expected Amount to Be Withdrawn at End of Year $0.00 $17,000 $17,000 $17,000 $17,500 $18,000
Table 9.14
The NPV function in Excel shows drop-down menus where you can enter numerical values for Rate, Value 1, and Value 2.
Figure 9.5 Dialog Box for NPV Function, Problem 2

Again, Rate is the interest rate; Value1, Value 2, and so on are the cash flows; and “Formula result” is the answer.

Let us apply the NPV function to our problem, as shown in Figure 9.6 and Figure 9.7.

A screenshot shows the excel data for problem 2. The data in Row 1 are Year, 0, 1, 2, 3, 4, and 5. These are entered in cells A1 through G1. The desired withdrawals each year are listed in row 2. In years 1 through 3 17,0000 is the desired withdrawal; in year 4 it is 17,500 and in year 5 it is 18,000. These values are entered in rows C2 through G2. The interest rate is 6%. This is entered in cell B3. The NPV is 72,753.49.
Figure 9.6 Applying the NPV Function, Problem 2: Excel Data
The NPV function in Excel shows the drop-down menu with the Rate cell value open parenthesis B3 close parenthesis and Value 1 open parenthesis C2 colon G2 close parenthesis. The formula result is $72,753.49.
Figure 9.7 Applying the NPV Function, Problem 2: Function Argument

Please note that the Rate cell value (B3) and the Value1 cell range (C2:G2) will vary depending on how you set up your spreadsheet.

The non-Excel version of the problem produces the same result: an NPV of $72,753.49.

Year 0 1 2 3 4 5
Desired Withdrawals
Each Year
$17,000 $17,000 $17,000 $17,500 $18,000  
Interest Rate 0.06          
NPV           $72,753.49
Table 9.15

Footnotes

  • 2The 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.
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

© Jun 8, 2023 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.