PMT and NPER functions in Excel - Time value of money

At each question the solution cell must contain the Excel formula (Function) that produced the answer. - Replace the existing numerical contents.- Also add a brief explanation of how the answer was derived and the significance of the question in understanding the Time Value of Money.

1. Alan had saved up $500,000. How much more must he save each year over the next 20 years in order to have a
total of $1 million? Alan earns 5% interest, compounded annually.
Answer: $9,878.71

Hint: Use the Excel PMT function.

2. You deposit $5,000 in an account that pays 8% interest per annum. How long will it take to double your money?
Answer: 9 years
Hint: Use the Excel NPER function.
Use the Rule of 72.

Solution Preview

See the attached file. Thanks

Time Value of Money
At each question the solution cell must contain the Excel formula (Function) that produced the answer. - Replace the existing numerical contents.- Also add a brief explanation of how the answer was derived and the significance of the question in understanding the Time Value of Money.

1. Alan had saved up $500,000. How much more must he save each year over the next 20 ...

Solution Summary

Solves two problems on time value of money using Excel formula (function) with brief explanation.

Using the present value function in MS Excel, verify that the present value of $100,000 to be received in five years at an interest rate of 16%, compounded annually, is $47,610. Calculate the present value of $100,000 for each of the following items (parts a-f) using these facts:
a. Interest is compounded semiannually
b. Int

4-1 If you deposit $10,000 in a bank that pays 10% interest annually, how much will be in your account after 5 years.
4-2 What is the present value of a security that will pay $5,000 in 20 years if securities of equal risk pay 7% annually?
4-3 Your parents will retire in 18 years. They currently have $250,000 and they t

Your girlfriend just won the Florida lottery. She has the choice of $15,000,000 today or a 20-year annuity of $1,050,000, with the first payment coming one year from today. What rate of return is built into the annuity?
A. 2.79%
B. 3.10%
C. 3.44%
D. 3.79%
E. 4.17%

I need to build an Excel spreadsheet for the calculations in the chart. The spreadsheet must include Excel formulas used (I have listed the Excel formulas- I am having a hard time plugging them in to the spreadsheet). I would also like the detailed formulas written out so that I can plug them in for later practice and understa

1. Calculate the difference between daily and annual compounding, given the following information: (a) PV: $52,000, (b) NPER: 30, and (c) RATE: 10%.
2. Calculate the PMT on a mortgage, given the following information: (a) PV: $439,000, (b) RATE: 4%, andNPER: 30.
3. Calculate the present value of a lump sum payment with the

How long would it take you to obtain $1,000,000 if you saved $2,500 quarterly and were able to obtain a 7% rate of return per year?
Calculate the current yield on a bond that has the following characteristics:
(a) NPER: 30, (B) Price--$1,125, (c) Coupon Rate: 6%, (d) Market Rate of Interest: 4%.
Calculate the Yield to Matu

1. Determine how much you would be willing to pay for an Annuity Due that has the following characteristics: (a) PMT: $5,500, (b) RATE: 8%, and (c) NPER: 15.
2. What is the current yield on a bond that has the following characteristics: (a) Price: $1,055, (b) Coupon Rate: 5%, (c) YTM: 4.6%, and (d) NPER: 22.
3. What is t

Ashley Cambry is planning for her retirement. She already has $12,500 in a retirement plan and will deposit $500 a month for the next 20 years. Her account manger says she will be earning 8.00% on an annual basis on this account at the time of retirement and Ashley plans to withdraw a sum each month during her 15 retiremen

Maria received an inheritance of $750,000 today
a. If she invest it at 8% compounded annually and takes nothing out, how much will she have at the end of 20 years?
b. If she take out $50,000 to but a car and invest the remainder at 8% compounded annually and takes nothing else out, how much will she have at the en