Create an Amortization Schedule in Microsoft Excel

» Create an Amortization Schedule in Microsoft Excel

CATEGORY: Excel Financial Formulas

VERSIONS: All Microsoft Excel Versions

Simple amortization schedule

See screen shot 1.

Formulas that do not appear in the figure:

The formula in cell C17 is =LOAN (LOAN is the name of cell C4).
The formula in cell C18 is =G17; copy the formula from cell C18 to all the cells in column C, starting from C18.

Amortization schedule with a grace period

The difference between a regular amortization schedule and one with a grace period is that in the latter, the repayment of the principal is delayed. The loan agreement stipulates the month in which the repayment of principal begins. The interest on the loan is calculated, and the first interest payment starts with the first month after the month that the loan was accepted.
See screen shot 2. Note that the formulas used are not PPMT and IPMT; these formulas are not appropriate when the calculations are not linear.

Amortization schedule for random payment

See the formulas in screen shot 3 for calculating the sum of the principal and interest in every payment.
The dates of the loan repayment are random. The interest is calculated according to the number of interest days divided by 365 days in a year.

Screenshot // Create an Amortization Schedule in Microsoft Excel

Rate this tip



1	2	3	4	5

RATING: 2.79
VIEWS: 212063

Email Tip

Print Tip

Add to My Tips

Create an Amortization Schedule in Microsoft Excel

Deni wrote on December 31, 1969 19:00 EST

Very Helpful.com

Good Tip

Aidan wrote on December 31, 1969 19:00 EST

That was very good, could you email your simple schedule to me @ a_jit@hotmail.com, so i can compare with mine, I had a little trouble trying to get it to function as i intended

Expand

Bing wrote on December 31, 1969 19:00 EST

How do I expand out the chart from the first payment on?

Grace Period...

Barato wrote on December 31, 1969 19:00 EST

Can someone tell me what is exactly placed into screen shot 2 at cell C13 (there is a reference but there is nothing there...) Very confusing, does not work properly..Please urgent help needed and appreciated!
Thank you in advance.

Having Multiple Interest Rates in Amortization Schedule

Denise wrote on December 31, 1969 19:00 EST

How do you create multiple interest rates in the Amortization Schedule so that the end balance will be equal to zeor

inq

Sphinx wrote on December 31, 1969 19:00 EST

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.