Excel Challenge 51: Build an Amortization Schedule

This Excel challenge emphasizes using Excel finance functions to track and analyze loan amortization schedules and financial forecasts, ensuring strategic alignment with long-term financial goals. By deploying advanced Excel techniques, you'll craft a comprehensive solution that extends beyond ordinary spreadsheet functionalities!

Everything you need to participate is available on this page. To get started:

• Watch the challenge video and review the instructions provided below.
• Download the Excel worksheet to complete the challenge tasks.

Dive in and test your skills!

Challenge objective

The objective of this challenge is to construct an Excel model that:

1. utilizes Excel's finance functions to accurately build and analyze an amortization schedule for tracking loan repayments over time.
2. employs Excel's built-in functionalities to calculate monthly payments, interest, principal reduction, and remaining balance, ensuring each aspect of the loan's progression is clearly visualized and managed.

Scenario

You are purchasing a property priced at $555,555. You plan to make a 25% down payment and finance the remaining amount through a loan. One bank advertises a 10-year term loan with an annual interest rate of 10%.

You would like to outline what your monthly payments would be, as well as the interest and total repayment you would be making over the term of the loan in order to determine if you should pursue this offer or look for other alternatives.

Create a simple loan amortization schedule template

• Cell D9: Calculates the monthly payment using the necessary function in Excel.
• Cell D8 : Lists the period numbers for each month over the 10-year term.
• Cell C15 onwards: Shows the next bank loan payment date, starting from February 1, 2025, and distributes it monthly until the end of the loan term.
• Cell D15 onwards: Lists the monthly bank payment dates, corresponding to each period from the starting date.
• Cell E15 onwards: Shows the interest payment for each month.
• Cell F15 onwards: Lists the principal payment for each month.
• Cell G15 onwards: Details the remaining balance after each payment.

First payment and beyond: The first payment is made in February 2025. Update the amortization schedule for each month, tracking the principal, interest, and remaining balance.

What will you learn? This practical challenge will help you to apply your knowledge of loans and amortization schedules in a real-world context, enhancing your understanding of finance and Excel functions.

As a bonus, the completed file can be used as a loan amortization schedule template for future reference.

Hint: You'll need to pull out the PMT function, and maybe brush up on some Excel date functions too.

How quickly can you wrap this one up? A few minutes? A few hours? Share your progress in our learner community on Slack!

---

Solution

Were you able to solve it? Here is our official solution:

As you know, our community members always have their own take! Check out their solutions here.

Want to simplify the numbers in your life? Give yourself a personal or professional upgrade by getting certified in Excel at your own pace.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial