112|EXCEL – Time Value of Money with PMT

BYU Student Author: @Mike_Paulsin , @Trent_Barlow
Reviewers: @Erick_Sizilio, @Jonathan_Weston, @Mark, @Millie_K_B
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

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.

Solution

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)

TIme: 25
Level: Intermediate
I got to refresh some thing and learn others
Challenge158_Data.xlsx

Time : An hour
Level : Intermediate

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 :smile:

Client.xlsx (36.5 KB)