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

Deb Ashby

#### Join the Excel conversation on Slack

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

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.
• Put yourself to the test!

Get the challenge file.

Can you solve it? Take the challenge!

## The challenge🤺

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:

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