How to calculate interest on home loan in Excel

When you take out a fixed-rate mortgage to buy or refinance a home, your lender takes three numbers and plugs them into a formula to calculate your monthly payment. Those three numbers are your principal, or the amount of money you're borrowing; your interest rate; and the number of months in your loan term. You can quickly create a spreadsheet in Microsoft Excel to perform the calculation for you--and, in the process, gain a greater understanding of just how a mortgage loan works.

Launch Microsoft Excel. Open a new workbook by pressing "Ctrl" and "N."
Type "Principal" into cell A1 on the Excel worksheet. Type "Rate" into cell A2. Type "Months" into cell A3.
Enter the amount of the mortgage principal in cell B1.
Enter the interest rate in cell B2. Just enter the number; don't use the percent sign. So, if your rate is 7 percent, just enter 7. If it's 5.75 percent, enter 5.75.
Enter the number of months in the loan term in cell B3. Most mortgages are for either 15 or 30 years. Enter 180 for a 15-year mortgage or 360 for a 30-year loan. If your loan is for some other number of years, simply multiply that number by 12 and enter the result in cell B3.
Enter the following formula in cell A4, beginning with the "equals" sign:

This converts your annual interest rate to a decimal figure by dividing it by 100, then breaks it down into a monthly rate by dividing it by 12.
Enter the following formula in cell A5, beginning with the "equals" sign:

This step takes into account the compounding of the interest over the life of the loan.
Enter the following formula in cell A6, beginning with the "equals" sign:

This takes all the data and boils it down to a multiplier that's applied to your principal to determine your monthly payment.
Enter the following formula in cell A7, beginning with the "equals" sign:

This applies the multiplier to your loan principal.
Right-click on cell A7 and select "Format Cells." Set the formatting to "Currency." Set "Decimal Places" to 2. Set the "Currency Symbol" to the dollar sign. Click "OK." This cell now gives you the amount of your mortgage payment based on your principal, interest rate and loan term.

Experiment with different principal amounts, interest rates and loan terms just by changing the values in cells B1, B2 and B3. The total payment in cell A7 will change to reflect the new figures.

Most lenders require that you pay your property taxes and homeowners' insurance premiums on a monthly basis, with 1/12 of the total tacked on to each mortgage payment. Those amounts are not included in this calculation. The result in cell A7 includes only the amount that goes to your lender--the total principal and interest due each month.

Let’s say you have bought a house with a bank loan, and you need to pay the bank every month in coming years. Do you know how much interest you will pay on the loan? Actually, you can apply the CUMIPMT function to figure it out easily in Excel.

Calculate total interest paid on a loan in Excel

Calculate total interest paid on a loan in Excel

For example, you have borrowed $100000 from bank in total, the annual loan interest rate is 5.20%, and you will pay the bank every month in the coming 3 years as below screenshot shown. Now you can calculate the total interest you will pay on the load easily as follows:

Select the cell you will place the calculated result in, type the formula =CUMIPMT(B2/12,B3*12,B1,B4,B5,1), and press the Enter key. See screenshot:

Note: In the formula, B2 is the annual loan interest rate, B2/12 will get the monthly rate; B3 is the years of the loan, B3*12 will get the total number of periods (months) during the loan; B1 is the total amount of loan; B4 is the first period you pay the bank, while B5 is the last period you pay the bank.

Now you will get the total interest you will pay. See screenshot:

How to create loan amortization interest calculator of Excel template?

No ratings yet. Be the first to rate!

Managing personal finances can be a challenge, especially when trying to plan your payments and savings. Excel formulas and budgeting templates can help you calculate the future value of your debts and investments, making it easier to figure out how long it will take for you to reach your goals. Use the following functions:

PMT calculates the payment for a loan based on constant payments and a constant interest rate.
NPER calculates the number of payment periods for an investment based on regular, constant payments and a constant interest rate.
PV returns the present value of an investment. The present value is the total amount that a series of future payments is worth now.
FV returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Figure out the monthly payments to pay off a credit card debt

Assume that the balance due is $5,400 at a 17% annual interest rate. Nothing else will be purchased on the card while the debt is being paid off.

Using the function PMT(rate,NPER,PV)

=PMT(17%/12,2*12,5400)

the result is a monthly payment of $266.99 to pay the debt off in two years.

The rate argument is the interest rate per period for the loan. For example, in this formula the 17% annual interest rate is divided by 12, the number of months in a year.
The NPER argument of 2*12 is the total number of payment periods for the loan.
The PV or present value argument is 5400.

Figure out monthly mortgage payments

Imagine a $180,000 home at 5% interest, with a 30-year mortgage.

Using the function PMT(rate,NPER,PV)

=PMT(5%/12,30*12,180000)

the result is a monthly payment (not including insurance and taxes) of $966.28.

The rate argument is 5% divided by the 12 months in a year.
The NPER argument is 30*12 for a 30 year mortgage with 12 monthly payments made each year.
The PV argument is 180000 (the present value of the loan).

Find out how to save each month for a dream vacation

You’d like to save for a vacation three years from now that will cost $8,500. The annual interest rate for saving is 1.5%.

Using the function PMT(rate,NPER,PV,FV)

=PMT(1.5%/12,3*12,0,8500)

to save $8,500 in three years would require a savings of $230.99 each month for three years.

The rate argument is 1.5% divided by 12, the number of months in a year.
The NPER argument is 3*12 for twelve monthly payments over three years.
The PV (present value) is 0 because the account is starting from zero.
The FV (future value) that you want to save is $8,500.

Now imagine that you are saving for an $8,500 vacation over three years, and wonder how much you would need to deposit in your account to keep monthly savings at $175.00 per month. The PV function will calculate how much of a starting deposit will yield a future value.

Using the function PV(rate,NPER,PMT,FV)

=PV(1.5%/12,3*12,-175,8500)

an initial deposit of $1,969.62 would be required in order to be able to pay $175.00 per month and end up with $8500 in three years.

The rate argument is 1.5%/12.
The NPER argument is 3*12 (or twelve monthly payments for three years).
The PMT is -175 (you would pay $175 per month).
The FV (future value) is 8500.

Find out how long it will take to pay off a personal loan

Imagine that you have a $2,500 personal loan, and have agreed to pay $150 a month at 3% annual interest.

Using the function NPER(rate,PMT,PV)

=NPER(3%/12,-150,2500)

it would take 17 months and some days to pay off the loan.

The rate argument is 3%/12 monthly payments per year.
The PMT argument is -150.
The PV (present value) argument is 2500.

Figure out a down payment

Say that you’d like to buy a $19,000 car at a 2.9% interest rate over three years. You want to keep the monthly payments at $350 a month, so you need to figure out your down payment. In this formula the result of the PV function is the loan amount, which is then subtracted from the purchase price to get the down payment.

Using the function PV(rate,NPER,PMT)

=19000-PV(2.9%/12, 3*12,-350)

the down payment required would be $6,946.48

The $19,000 purchase price is listed first in the formula. The result of the PV function will be subtracted from the purchase price.
The rate argument is 2.9% divided by 12.
The NPER argument is 3*12 (or twelve monthly payments over three years).
The PMT is -350 (you would pay $350 per month).

See how much your savings will add up to over time

Starting with $500 in your account, how much will you have in 10 months if you deposit $200 a month at 1.5% interest?

Using the function FV(rate,NPER,PMT,PV)

=FV(1.5%/12,10,-200,-500)

in 10 months you would have $2,517.57 in savings.