Microsoft Excel

3 minute read

Excel Challenge 33: What-If Analysis to Calculate Monthly Payments

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Are you ready to flex some intermediate-to-advanced Excel skills?

Everything you need to participate in the challenge can be found on this page. To take part:

  • First, watch the challenge video and read the instructions below the video.
  • Review the previously published videos and articles on which the challenge is based.
  • Download the Excel worksheet you will use to complete the challenge tasks.
  • Put yourself to the test!

Get the challenge file.

Can you solve it? Take the challenge!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.


The challengešŸ¤ŗ

Here’s the scenario to be solved from the download file:

We want to buy a house and need to ask the bank for a loan of $400,000. 

The bank offers the following loan terms:

  • Loan Amount: $400,000.
  • Interest Rate: 4.25% (per annum).
  • Payment Period: 30 years.
  • Payment Due: End of the month.

Our monthly budget for this loan is $2500.00. 

Part 1: Calculate the monthly payments

We need to calculate our monthly repayments to see if we can realistically afford the loan and if it falls within our budget. 

Note the following:

  • All calculations must be monthly, not yearly. 
  • We are not making a bulk payment (future value) at the end of the loan to clear the balance down to zero.
  • The monthly payment should be displayed as a positive value. 

Excel PMT function

Part 2: Recalculate the loan

Once we have performed this calculation, we can see that it falls below our monthly budget. 

Use What-If Analysis tools to work out how much more money we could afford to borrow from the bank if we were paying our maximum budget value of $2500.00 per month.  

What if analysis ExcelHow quickly can you complete this challenge? Fifteen minutes? Ten? Less?


HintsšŸ˜‰

If you’re just getting started with Excel, all the knowledge you need to solve this challenge has been shown in the following resources:

Read:

GoSkills Excel challenge

   How to Use PMT in Excel

   A Beginner's Guide to What If Analysis in Excel 

   Excel Goal Seek Explained (Step by Step Guide)

 

Watch:

And if you need it, we have even more Excel resources here. If you’re already proficient with Excel, this should be quite easy. Maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!

Get the challenge file.

Can you solve it? Take the challenge!


The solutionāœØ

We hope you enjoyed taking part in this challenge. These skills and more are covered in our Excel for Business Analysts course, as well as the popular Excel Basic and Advanced course. We have a 7-day free trial waiting for you! šŸŽ

And stay tuned to the GoSkills Excel Resource hub for more Excel challenges!

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.