Select Page

Most of us will need to take out a loan at some stage of our lives, whether it’s for buying a house, a car or something else less exciting. Tracking loan repayments is crucial to staying on top of our finances and in this video, I’ll show you how to easily create a loan tracker in Excel.

You’ll just need to enter the loan amount, the annual interest rate and the number of payments and Excel will take care of calculating your monthly repayment and the total amount to be repaid.

The tracker also helps you keep an eye on how much you’ve paid off and what’s left, automatically adjusting based on your repayment schedule.

The video covers how to create a simple loan calculator (cols A and B in the screenshot above) and how to create a repayment schedule (cols E, F and G above). In the repayment schedule part I cover 2 methods. One can only be used in Excel 365. The other can be used in any version of Excel although if you have Microsoft 365 I recommend using the first method – it’s simpler and more flexible.

Loan Calculator (PMT Function)

Repayment Schedule (M365 Version)

Repayment Schedule (Non-M365 Version)

You can download a copy of the file I used in the video from the YouTube description

For reference, here are the formulas that I used in the video:

• =PMT(B2/12,B3,-B1)
• =B3 * B5
• =SEQUENCE(B3)
• =B5*SEQUENCE(B3)
• =B6-B5*SEQUENCE(B3)
• =IF(ROW(A1)<= \$B\$3, ROW(A1), “”)
• =IF(E2=””,””,\$B\$5*E2)
• =IF(E2=””,””,\$B\$6-F2)