A loan amortization schedule in Excel can be created either by using a template or creating a custom schedule - the easiest method will be using the template which is already in your Excel package. Alternatively, we can create a custom loan amortization schedule using IPMT and PPMT functions.

First we will look how to use the inbuilt template in Excel to create loan amortization schedule.

Open Excel and click on "File" tab on the left hand side. Then click 'New' tab on the dropdown. You will see on the right all the templates available.

Click on the 'Sample Templates', and you will see the 'Loan Amortization Template' there. Click on this and a new Excel work book will open with loan amortization schedule.

This template is very simple and easy to use; you are just required to enter various details in the template. Loan amount is the amount you are borrowing; and the annual interest rate is the rate bank is charging you. You should also enter loan duration which should be in years. If you are required to pay monthly interest, enter 12 in the cell next to Number of payments per year. If you are paying quarterly, you should enter 4 here and so on. Start date of loan is a mandatory field and you should enter in the date format mo/date/year. Optional extra payments and lender name are optional. If you are planning to make any additional principal payment, put the amount in the cell next to it. This amount will have an impact on your loan amortization schedule.

Now that you have the loan amortization schedule template, you should play around it and see how it works. Wait! did you notice that it is protected? No worries. Right click on the loan amortization schedule tab, and you will see unprotect sheet tool. Click on it, and the template will be unlocked. No password is required. Have fun!

If you need, you can change the currency format and colors since it is unprotected.

Now let's create at a customized loan amortization schedule. We can do this very easily by using Excel's IPMT and PPMT functions.

Let us look at the syntax of these functions.

[alert close="no"]

= IPMT(rate, per, nper, pv, [fv], [type])

This formula calculates the amount of interest for the given period.

Where rate is the interest rate per period; Per is the period for which you want to find the interest and must be in the range 1 to nper. Nper is the loan duration.

If your loan duration is 5 years and you are trying to find the interest payment for the 2^nd year, Per will be 2 and Nper will be 5.

Pv is the present value or simply the loan amount.

Fv is an optional input and it is the future value of out standing loan amount after all the payment has been made. The default is 0, and we will keep it unchanged.

'Type' again is an optional input. It can be either 0 meaning payment is done at the end of the period; or 1 meaning payment is done at the beginning of the period. The default value is 0, and we will not input any number here as the payments are generally made at the end of the period.

PPMT function has similar syntax and calculates the principal payment.

= PPMT(rate, per, nper, pv, [fv], [type])

This formula calculates the payment on the principal for a given period. Syntax is same as the IPMT formula explained above.

Alternately, we can also use PMT function to calculate the total payment.

Example: Imagine you are working on a real estate project financing model and wondering how to build a loan amortization schedule to incorporate in your cash flow.

Assume that the construction cost is 250,000,000 and 80% of it is funded by the debt. The interest cost is 6% and loan duration is 5 years. The repayment is annual and there is no repayment during the construction duration of 2 years.

You can easily construct the loan amortization schedule using IPMT and PPMT functions. See the model below:

In coming post will discuss how to create a customized and dynamic loan amortization schedule, which you should be able to use in any given situation.

What do you think about this post on loan amortization schedule, use the comment section below. You can also download the unprotected Excel workbook containing both the examples.

Download the file from below link.

Check out more useful articles on Our Blog Page

Are you someone involved with real estate feasibility?

We are excited to launch the next generation of real estate feasibility software by April 2023.

claim your 15% early-bird discount

Know more about the product features

Learn More