1. Retrieve the file CFM0510P.XLS. Enter the appropriate financial function comm
ID: 2786320 • Letter: 1
Question
1. Retrieve the file CFM0510P.XLS. Enter the appropriate
financial function command in cell B7 that solves the following problem: Susan Robinson is planning for her retirement. She is 30 years old today and would like to have $600,000 when she turns 55. She estimates that she will be able to earn a 9 percent rate of return on her retirement investments over time; she wants to set aside a constant amount of money every year (at the end of the year) to help achieve her objective. How much money must Robinson invest at the end of each of the next 25 years to realize her goal of $600,000 at the end of that time?
2. Invoke a copy command in cell B11 to fill cells B12 through
B35.
3. Create a formula in cell C12 that solves for the total
compound value of the retirement fund following the second
ordinary annuity contribution.
4. Adapt the formula in cell C12 to cells C13 through C35.
Hint: Invoke a copy command, being mindful of the issue of
absolute versus relative cell addresses.
5. Verify that the dollar amount reported in cell C35 justifies
the conclusion reported in cell D35.
6. In cell B37 compute the total ordinary annuity contributions
made during the life of the retirement fund.
7. Enter the appropriate financial function command in cell B47
that solves Problem 10 assuming annuity due contributions are
planned.
8. In cell B48 compute the total annuity due contributions made
during the life of the retirement fund.
The excel sheet is as follows:
Explanation / Answer
HI,
Please follow the below steps
Answer Point - 1 : =PMT($B$6,$B$5,0,$B$4,0)
Explanation Point 1 - this formula gives you the ordinary annuity occuring at he end of every period. the inputs in the formula goes as PMT(rate of interest, term,present value,future value,0 for ordinary annuity and 1 for annuity due)
Answer Point - 2 : =PMT($B$6,$B$5,0,$B$4,0) Drag the formula till B35 to get the results. the formula works as explained in the previous point
Answer Point - 3 : In cell C11 just copy the formula =B11. This will give you the annuity value for the first year. there will be no interest applied on this amount as the annuity is paid at the end of first year. the interest on this amount will be earned at the end of second year.
In cell C12 copy formula =C11*(1+$B$6)+B12
Answer Point - 4 : Drag the formula in C12 till C35. This will give you the desired compounded value.
Answer Point - 5 : C35 gives an amount of $600,000 which is the required objective after 25 years.
Answer Point - 6 : =SUM(B11:B35) It'll give you the total annuity contributions
Answer Point - 7 : =PMT($B$41,$B$40,0,$B$39,1) thisi will give you the annuity due contributions. please note that for this point I have considerred that you have $600,000 (financial objective) in cell B39, 25 (term) in cell B40 and 9% (rate) in cell B41.
Answer Point - 8 : =B47*B40 Total contribution can be found out by multiplying the annuity due calculated in cell B47 by the tenure in cell B40.
Good Luck!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.