Overview
Imagine you’re a financial advisor helping a client plan for retirement. Your client is concerned that they won’t have enough money saved up to retire comfortably, and they’re not sure how much they need to save each month to hit their retirement goal.
To help your client out, you decide to create an Excel worksheet that will take in their current age, retirement goal, and age of retirement, and calculate how much money they need to save each month to reach their retirement goal.
The worksheet should take into account the time value of money and an appropriate interest rate to provide a realistic estimate of the required monthly savings. By inputting their preferences into the sheet, your client will have a clear idea of how much they need to save each month in order to retire comfortably at their desired age.
With your help, your client can feel more confident about their retirement plan and take the necessary steps to achieve their financial goals.
Instructions
Create labelled spaces for the user to input their current age, retirement goal, retirement age, current retirement savings, and industry average interest rate
Calculate the number of years until retirement by subtracting the current age from the retirement age
Set the industry average interest rate to 5%
Use the PMT**()** function to calculate the required monthly savings. When putting in the parameters, soft-code (ie reference a cell, do not type in the numbers you want to use). The function takes the following parameters:
rate : the interest rate per period
nper : the total number of payment periods
pv : the present value of the investment (in this case, the current retirement savings)
fv : the future value of the investment (in this case, the retirement goal)
type : specifies when the payment is made (in this case, ‘end’ because the payment is made at the end of each month). For the purposes of this exercise, you may leave this field blank; it is optional and will default to ‘end.’
Create a table showing progress toward the retirement goal at the end of each month. Assume a starting month of January 2024, and that the user’s birthday is January 1st (it has already happened this year). Each entry in the table should show the month and year, the user’s age at that date, the beginning monthly balance, the interest earned that month on the principal, the contribution made by the user (the payment calculated in Step 4), and the ending monthly balance.
Cool problem and interesting to see the if logic being used. I need to practice if statements and iterative functions more. I ran into some problems when it came to dynamic sizing for the financial part of the savings table. I learned a lot. 112TVMProblem_TechHub_Strong,Nathan.xlsx (99.8 KB)
It took me longer than I wanted since its been a while since I touched an Excel sheet and its functions so I really needed to learn the stuff all over again but overall, A great practice question! I definitely learned a lot
It took me a little longer to complete this than I thought but it was a great exercise. I refreshed up on my IF statement skills when building the table. Definitely a great practice challenge! Client Challenge.xlsx (1.2 MB)
Time to complete: 40 minutes
Rating: Moderate
I had to use the solution video for a hint on how to get the column formulas right. Once I got the hint and made a quick adjustment the rest of the challenge became much easier. retirement calculator.xlsx (91.6 KB)
Time to Complete: 45 min
Difficulty: Intermediate
I enjoyed this challenge as it gave me a quick refresher on excel. I may even set one of these up for myself in the future challenge112jackroche.xlsx (67.6 KB)
Time to complete: 40 min
Difficulty: Intermediate
This was an interesting challenge that used a function I hadn’t used in a while. Excel Challenge.xlsx (299.7 KB)
