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

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!

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. 

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.  

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

	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!

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!