Selected applications.
1. Time value of money(FV,PV,Pmt,Nper,rate)
Calculating the future value of a present single sum
Example 1: Fv of single sum
Your client has $500,000 in an IRA and has asked you to estimate its value when the client reaches
retirement age in eight years, assuming a 6% return each year.
Example 2: Fv at variable interest rate
Your client has $500,000 in savings with eight years left before retirement. As an investment
strategy, the client would like to adjust the asset allocation of her investments over time, evolving
from a more aggressive strategy during the earlier years into a more conservative investment
approach as she approaches retirement age. Thus, she projects to earn a 10% annual return during
the first two years of the investment period, and 8%, 6%, and 4% returns over each of the next two-
year periods, respectively. Calculate the future value of the retirement savings.
Example 3: Calculating future value of annuity with the FV function
Example A: Your client would like to contribute $12,000 to a retirement account at the beginning of
each year for the next 20 years, earning an annual return of 6%.
Example B: A more likely scenario might be for your client to make monthly deposits to his
retirement account. Assume your client makes deposits of $1,000 at the end of each month for 20
years and earns 6% per year on his investments over that time.
Example C: An even more likely scenario would see your clients already having retirement savings
and asking you to project the future value of their current savings combined with additional monthly
contributions. Using the information from Example B, let's further assume that your client already
has accumulated $200,000 in retirement savings.
Example 4: Calculating the present value of a future single sum
Example A: A client has a desired retirement savings goal of $2 million to be achieved seven years
from now. She plans on making only one deposit into her account, and an annual return of 6% per
year is expected. By applying the PV function, the formula in cell B12 of the screenshot "Using the
PV Function," =PV(0.06,7,0,-2000000), calculates that the client needs to deposit $1,330,114 in her
retirement account today to achieve her goal of $2 million in seven years.
Example 4: Calculating PMT
Your client is buying a house for $300,000 and would like to determine the amount of the monthly
mortgage payment. The client can obtain financing for $240,000 over a 30-year period with a fixed
annual interest rate of 4%.
Example 5: Calculating annual and monthly interest rates
Your client desires to have a retirement account balance of $2 million in 25 years. The client plans
on making monthly deposits of $1,500 and would like to know the annual return required to reach
her goal
2. Project selection
Compute the (i) net present value and (ii) internal rate of return of the following capital budgeting
projects. The firm’s required rate of return is 12 percent.
Projects
Year Zeta Omega
0 $(50,000) $(45,000)
1 20,000 42,000
2 15,000 9,000
3 30,000 1,850
Solutions to part (a) and (e):
Project Zeta Project Omega
Year CF PV of CF Σ(PV of CF) CF PV of CF Σ(PV of CF)
0 $(50,000) $(50,000.00) $(50,000.00) $(45,000) $(45,000.00) $(45,000.00)
1 20,000 17,857.14 (32,142.86) 42,000 37,500.00 (7,500.00)
2 15,000 11,957.91 (20,184.95) 9,000 7,174.74 (325.26)
3 30,000 21,353.41 1,168.46 1,850 1,316.79 991.54
NPV = $1,168.46 NPV = $991.54
IRR = 13.26% IRR = 14.03%
Following is the computation of the present values of the annual cash flows and the NPV for
Project Zeta using the equation method:
20,000 15,000 30,000
NPV 50,000
(1.12)
1
(1.12)
2
(1.12)3
50,000 20,000(0.892857) 15,000(0.797194) 30,000(0.711780)
50,000 17,857.14 11,957.91 21,353.40
1,168.45 (rounding)
To compute the NPV using a financial calculator, you must use the cash flow register. Enter
the cash flows in the following order:
CF0 = –50,000
CF1 = 20,000
CF2 = 15,000
CF3 = 30,000
Then input I = 12
At this point, you have entered the values shown in the above equation. Solving for NPV, you
should find NPV = 1,168.46. To compute the IRR, enter the cash flows the same as when
computing the NPV, but solve for IRR = 13.26%.
To compute the NPV and IRR for Project Omega, enter its cash flows is the appropriate order
and solve for NPV = $991.54 and IRR = 14.03%
3. Mixed cost analysis
Using the data provided in the table below to
i. develop the total cost equation
ii. determine the total cost needed to produce 4,500 units
Year Production Cost
1 800 93,000
2 1100 114,000
3 1200 119,000
4 950 103,000
5 1300 126,000
6 1250 124,000
7 1000 107,000
8 1050 110,000
9 1000 105,000
10 900 100,000
11 1050 110,000
12 1200 119,500