how to make a mortgage amortization schedule in excel

The amount you put into cell B3 should now also appear in cell B6. In cell B6 put this formula: =Principal.Give cell B3 a name, such as 'Principal'.In cell B3 put how much you are borrowing.In cell B2 put the number of months you need to pay.

Given some basic information such as how much you are borrowing (your principal), what your interest rate is, and how many monthly payments you need to make, you can then come up with your amortization schedule. At its simplest, a mortgage payment consists of two parts: principle and interest. It actually is fairly easy to come up with the right calculations. Problem is, she doesn't know enough about finance to know which of the financial worksheet functions she should use to do the calculations. We can visualize the impact with a nice chart (requires some extra work) like this:ĭo check the download workbook for details on how the chart is setup.Mary would like to use Excel to create an amortization schedule for her home mortgage. Go ahead and play with the table by typing some values in the “Extra payment” column. Step 3: Your mortgage will end when the “Eff.

Closing Balance is opening balance minus principal paid minus extra payment.Ĭomplete this table with necessary formulas and fill everything down.

Extra Payment is the input column where we can type any extra payments.

We can get this with the PPMT() function.

Principal Paid is the amount of principal paid in each month.

=ROUND(NPER($E$7/12,$E$10,$D13),0) will tell us how many months it is rounded. We can use NPER function to get the answer here.

Effective term is how long it would take you to pay off the mortgage based on the opening balance, and agreed upon monthly payment (calculated in Step 1) and interest rate (Cell E7).

For subsequent months, this will same as previous month’s closing balance.

Opening Balance is same as loan amount for month=1.

Related: Read about SEQUENCE and other Dynamic Array functions in Excel. You can use =SEQUENCE(360) to automatically generate all the months. So, set up a range of 360 months (or longer if you want to cater for longer mortgages). In my case, let’s say loan is $500,000, term is 20 years and APR (Interest rate) is 5.35% per annum.Īs extra payment will bring down the outstanding loan term, we need to set up an amortization table to see the impact clearly. Step 1: Calculate the monthly (or weekly / fortnightly) payment:Īssuming you have the Loan amount, term & APR in three cells E5, E6 & E7, we can use the PMT() function to calculate the periodic payment.

How to make a mortgage amortization schedule in excel