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

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

Client.xlsx (36.5 KB)

Time: 45 minutes
Level: Intermediate

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: 45 Minutes
Level: Intermediate

I enjoyed this challenge and had the opportunity to brush up on some basic excel skills.
Challenge EYRAC.xlsx (40.6 KB)

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)

It took be about a half hour to complete this challenge and was not difficult at all!
Excel challenge.xlsx (82.3 KB)

Time to Complete: 40 min
Rating: Intermediate

It was fun reviewing some familiar functions and also learning new ways of organizing data.
Excel Challenge 112.xlsx (65.5 KB)

Excel 112 Challenge.xlsx (64.9 KB)
I enjoyed working through this excel challenge. It took me about 45 minutes.

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)

Techhub training challenge - Hunter S.xlsx (69.3 KB)
Was a good challenge using those functions again.

Time to complete: 30
Rating: Intermediate

Was fun doing the IF statements

Practice.xlsx (948.0 KB)

PMT Excel Challenge - Hackenbracht.xlsx (80.6 KB)
Time to complete: 30-45 minutes
Difficulty: Intermediate

I needed to check the answer key to fix my IF statements, but otherwise very doable!

Great challenge!

TVM with PMT.xlsx (19.8 KB)

Time to Complete: 20 mins
Difficulty: Beginner Friendly

I have not had much experience with excel so that was a great opportunity for me to become more familiar to its functionality

TechHub Training.xlsx (9.4 KB)
took around 25 minutes, wasn’t that bad

30 minutes, intermediate
LG- Excel Challenge.xlsx (11.5 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)