Build Your Own Loan Calculator 📆

This will show you how to build your own loan calculator in Excel.

Yes, there are plenty of loan calculators on the web, but they often require you to share your personal financial information, and often come with a submit button which leads to a “friendly sales call”.

No thanks. I’ll do it myself.

Working out repayments in Excel:

If you ONLY want to know the repayments, you can use Excel’s built-in function 

=PMT(rate, nper, pv, [fv], [type])

The PMT function has the following inputs:

  • Rate: The interest rate for the loan per period

  • Nper:  The total number of payments for the loan. (Not the number of years!)

  • PV: The present value, also know and the loan principal. This is how much you are borrowing.

  • FV: (Optional) The future value, or the outstanding value remaining at the end of the loan. Many car leases will have a “balloon payment” where you pay the final amount outstanding to keep the car. This is unusual for mortgages since the aim to the pay off the loan entirely. If FV is omitted, Excel just assumes 0 (zero) which is the loan is fully paid off.

  • Type: (Optional) when payments are due. This can be either:

    • 0 (zero) [default] payments are made at the END of the period (technically known as an “ordinary annuity”), or

    • 1 repayments are made the START of the period (technically this is an “annuity due”).

Things to note: interest rates are often quoted in the annual form, but loans are often repaid more frequently (monthly or fortnightly). As such, you will need to adjust some of your inputs: RATE and NPER.

eg: A car loan of $40,000 for 9% pa repaid monthly over 5 years. 

RATE = 0.09 / 12 = 0.0075
NPER = 5 * 12  = 60

The RATE is the interest per period, so we split up the 9% by 12 months which is 0.75% per month compounding. Likewise the NPER is the number of periods, in this case, the number of months over the life of the loan, being 5 years *12 month = 60 months.

=PMT(0.0075,60,40000)

=PMT(0.0075,60,40000)

Final answer: $830.33 per month.

The output will be negative because in finance we distinguish between cash flows made to or from the counterparty. Here when we originally borrow the money we have a cash inflow of $40,000, so that’s positive. But when we repay the loan, that’s a cash outflow so it automatically displays as a negative number.

If the negative number confuses you, just put a minus sign at the start of the function

=-PMT(0.0075,60,40000)


The math for loan repayments:

But we shouldn’t trust black boxes we don’t understand. This is what the PMT function is doing:

Again using the same example: A car loan of $40,000 for 9% pa repaid monthly over 5 years.