Part I:

As a financial planner a client comes to you for investment advice. After meeting with him and understanding his needs, you offer him the following two investment options:

Option 1 (refer to section on Mathematics of Finance in your text.): Invest $23,000 in a savings account at 4.25% interest compounded quarterly.

Option 2 (refer to section on Mathematics of Finance in your text): Invest into an ordinary annuity where $5,000 is deposited each year into an account that earns 6.6% interest compounded annually.

SPREADSHEET:

Set up the formula for compound interest for Option 1 and the formula for Future Value of an Annuity for Option 2 in an Excel spreadsheet to calculate the amount earned at the end of 5 years.

http://www.jiskha.com/display.cgi?id=1292938297

1. Pt = Po(r + 1)n.

r = (APR/4) / 100 = (4.25/4) / 100 = 0.010625 = Quarterly percentage rate.

n = 4 comp/yr*5 yrs = 20 comp periods.

Pt = 23000(1.010625)^20,
Pt = 23000 * 1.2354 = 28413.75

Int. = Pt - Po,
Int. = 28413.75 - 23000 = 5413.75.

To set up the formulas in an Excel spreadsheet to calculate the amount earned at the end of 5 years for both Option 1 and Option 2, follow these steps:

1. Open a new Microsoft Excel spreadsheet.

2. In cell A1, type "Initial Investment" to label the first column.

3. In cell B1, type "Interest Rate" to label the second column.

4. In cell C1, type "Compounding Frequency" to label the third column.

5. In cell D1, type "Years" to label the fourth column.

6. In cell E1, type "Future Value" to label the fifth column.

7. In cell A2, enter the initial investment amount for Option 1, which is $23,000.

8. In cell B2, enter the interest rate for Option 1, which is 4.25%.

9. In cell C2, enter the compounding frequency for Option 1, which is quarterly. You can use the value 4 for quarterly compounding.

10. In cell D2, enter the number of years for Option 1, which is 5.

11. In cell E2, enter the formula for calculating compound interest using the PMT function in Excel. The formula would be "=PMT(B2/C2,C2*D2,A2,0)". This formula calculates the future value of the investment using the given parameters.

12. In cell A4, type "Annual Deposit" to label the first column for Option 2.

13. In cell B4, type "Interest Rate" to label the second column for Option 2.

14. In cell C4, type "Compounding Frequency" to label the third column for Option 2.

15. In cell D4, type "Years" to label the fourth column for Option 2.

16. In cell E4, type "Future Value" to label the fifth column for Option 2.

17. In cell A5, enter the annual deposit amount for Option 2, which is $5,000.

18. In cell B5, enter the interest rate for Option 2, which is 6.6%.

19. In cell C5, enter the compounding frequency for Option 2, which is annually. You can use the value 1 for annual compounding.

20. In cell D5, enter the number of years for Option 2, which is 5.

21. In cell E5, enter the formula for calculating the future value of an annuity using the FV function in Excel. The formula would be "=FV(B5/C5,D5,-A5,0)". This formula calculates the future value of the annuity using the given parameters.

22. Finally, you will see the calculated future value for Option 1 in cell E2 and for Option 2 in cell E5.

Remember to format the cells as currency or with the desired number of decimal places to display the results accurately.