Today is YOUR birthday. Use a base age of 23 plus the month of your actual birthdate (e.g. January is 1, June is 6, December is 12) to calculate your age for this project. Your age is: 34

• You are planning to retire when you turn 65.
• You currently have $25,000 in your retirement account.
• Based on actuary tables, you expect to live to be 100 years old.
• During each year of retirement you want to be able to withdraw $50,000 from your retirement account (at the beginning of the year).
• The interest rate is 3%.
• You plan to make deposits into your retirement account on your birthday, each year until you retire.

How much will you need to deposit into your retirement account at the beginning of each year (starting today) until you retire, so that you can withdraw $50,000 each year during retirement, and have an empty bank account at the end of your 100^th year?

Instructions:

1. Download the Saving for the Future Template from Canvas.
2. Input your age for this project into cell A
3. Delete any unnecessary rows for years that you will not use (i.e. if you were born in March, your age would be 26, and you would delete rows 4 & 5 for Year 24 and Year 25).
4. Link cell B4 to cell B2.
5. Link each years Ending Account Balance to the next years Beginning Account Balance using relative references.
6. Link cells C4:C44 to cell C2 using absolute references.
7. Link cells C45:C80 to cell D2 using absolute references.
8. Create a formula in cells D4:D80 to calculate Balance after Deposit/Withdraw using relative references.
9. Create a formula in cells E4:E80 to calculate Interest Earned using absolute and relative references.
10. Create a formula in cells E4:E80 to calculate Ending Account Balance using relative references.
11. In Cell C2, Calculate the Annual Deposit required.
    1. In cell F2, calculate the PV of making a series of $50,000 payments, starting at the beginning of year 65 and ending at the beginning of Year 100. This represents the required Ending Account Balance at the end of Year 64 (when you retire).
    2. In cell C2, calculate the payment required to achieve the desired Ending Account Balance at the end of Year 64 (the amount in cell F2).

(incorrect) Attempted Work: Annual Deposit =PMT(3%,(31),25000,-F2)

Amount required @ Year 64 =PV(3%,(100-65+1),-50000,0,1)

Please Use same Excell format in example, I tried plugging in everthing but it wont Zero out at the end and the number is negative meaning I needed to find a higher Annual deposit amount

I also cant find the correct required amount

Your age Beginning Balance Annual deposit Annual withdrawal Interest Rate Required @ Year 64 Your age Beginning Account Deposit/withdrawal Balance Balance after Deposit/withdrawal Interest Earned Ending Account Balance 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92. 93 94 95 96 97 98 99 100