WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Excel Modeling » Amortized Loan Formula

Amortized Loan Formula

By Rishab NigamRishab Nigam | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What is Amortized Loan Formula?

The amortized loan formula deals with the determination of annual or monthly payment that the borrower has to make to the lender for the loan undertaken by them. The Annual payment is composed of annual interest payments and the annual portion of the long-term debt. The interest component in the annual payment remains to be high during the initial tenure of the loan, however, during the ending tenure, the principal components dominate the annual payment.

The annual payments could be determined using the present value of the annuity formula. The amortized payment depends upon the rate of interest, tenure of the loan, borrowed sum, and the nature of compounding as per agreement between the lenders and the borrowers. The amortized payment is determined to let the lender anticipate how much it would get through the lend sum to be serviced by the borrower.

The formula is expressed as follows: –

Amortized Loan Formula = [Borrowed Amount * i * (1+i) n] / [(1+i) n – 1)

Here,

  • The rate of interest is represented as i.
  • The tenure of the loan is represented as n.

Amortized Loan Formula

Explanation

The amortized loan can be calculated by using the following steps:

Step 1: Firstly, the borrower has to determine the loan amount. The borrower citing his capacity and with the availability of loan, calculators determine the loan amount which they desire so that they can fill up the shortfall of funds.

Step 2: Next, determine the rate of interest that the lender would be most likely to lend the amount. Basis the conditions of the economy, and as per the expectations of lenders, there could be a specific range of interest rates available to the borrowers. The borrowers have to choose the best rate of interest from the available options.

Step 3: Next, determine the tenure of the loan, which the borrower believes he or she could service basis the paying capacity of the borrower.

Step 4: Next, determine the product of the borrowed sum, rate of interest, and with the sum of unity and rate of interest having the power of tenure of the loan.

Step 5: Next, deduct the sum of unity and rate of interest having the power of tenure of the loan and the unity.

Step 6: Next, divide the resulting value from step 4 with the resulting value of step 5 to arrive at the amortized loan amount to be serviced by the borrower that the borrower has to pay to the lender to amortize the loan.

Popular Course in this category
Sale
Financial Modeling Course (with 15+ Projects)
4.9 (927 ratings)
16 Courses | 15+ Projects | 90+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Examples of Amortized Loan Formula

You can download this Amortized Loan Formula Excel Template here – Amortized Loan Formula Excel Template

Example #1

Let us take the example of a borrower who is looking for finance for his new home. There is one lender who wishes to lend a loan of $2 million with a rate of interest of 8 percent for the tenure of 20 years. Help the borrower determine the monthly amortized loan amount.

Use the following data for the calculation of Amortized Loan Amount

Amortized Loan Formula Example 1

Calculation of monthly amortized loan is as follows –

Example 1.1

  • =[$2,000,000 x 0.08 / 12 x (1+0.08 /12) 12×20] / [(1+0.08/12) 12×20 – 1)
  • = [$65,690.70] / [3.926802771]

Monthly Amortized Loan Amount will be –

Amortized Loan Formula Example 1.2

  • Monthly Amortized Amount = $16,728.80

If the borrower undertakes the loan, then he has to service a monthly amortized amount of $16,728.80 for the period of 20 years.

Example #2

Let us take the example of a borrower who is looking for finance for his new car. There is one lender who wishes to lend a loan of $0.5 million with a rate of interest of 8 percent for the tenure of 5 years. Help the borrower determine the monthly amortized loan amount. Determine the monthly amortized amount as displayed: –

Use the following data for the calculation of Amortized Loan Amount

Amortized Loan Formula Example 2

The calculation of monthly amortized loan is as follows –

Example 2.1

  • = [$500,000 x 0.08 / 12 x (1+0.08 /12) 12×5] / [(1+0.08/12) 12×5 – 1)
  • = [$4,966.15] / [0.489845708]

Monthly Amortized Loan Amount will be –

Amortized Loan Formula Example 2.2

  • Monthly Amortized Amount = $ 10,138.20

If the borrower undertakes the loan, then he has to service a monthly amortized amount of $ 10,138.20 for five years.

Relevance and Use

To successfully service the loan, the borrower has to determine the amortized loan amount that he has to service throughout the tenure of the loan. Amortized Loan is considered to be advantageous if the borrower makes early payments at critical junctures to reduce the principal balance, which would, in turn, lower the high-interest payments. Before taking additional debt, it is advisable to assess the existing open accounts of loans to avoid the situation of a debt trap.

The amortized loan amount should be at least 35 percent of the annual or monthly income. Whenever the rate of interest falls, always maintain the same value of amortized amount and reduce the tenure of the loan. This would ensure faster servicing of loans within the allocated time frame.

Amortized Loan Formula in Excel

Now, let us take the excel example to illustrate the concept of the amortized loan in the excel template below. The PMT function is the finance function of the excel, which helps in the determination of an amortized loan amount. The syntax of pmt is represented as follows: –

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

Here,

  • The rate of interest is represented as a rate.
  • The number of periods is represented as nper.
  • The loan amount is represented as the pv.
  • The future value is represented as fv.
  • The pmt value to be determined at the or beginning of the period is represented by type.

Let us consider the examples in the excel to further illustrate the concept of an amortized loan.

Example #1

Use the following data for the calculation of the Amortized Loan Amount in excel.

Amortized Loan Formula Example 3

Calculation of Monthly Payment using the PMT function is as follows,

Amortized Loan Formula Example 3.1

  • =PMT(8.00%/12,20*12,-$2000000)

Monthly Payment will be –

Example 3.2

  • Monthly Payment = $16,728.80

Similarly, let us consider the example 2 in the excel to further illustrate the concept of amortized loan. The table provides a detailed explanation of the risk premium.

Example #2

Use the following data for the calculation of the Amortized Loan Amount in excel.

Example 4

Calculation of Monthly Payment using the PMT function is as follows,

Example 4.1

  • =PMT(8.00%/12,5*12,-$500000)

Monthly Payment will be –

Example 4.2

  • Monthly Payment =$10138.30

Recommended Articles

This has been a guide to the Amortized Loan Formula. Here we discuss the formula for calculation of Amortized Loan along with practical examples and a downloadable excel template. You can learn more about financial analysis from the following articles –

  • Exchange Rate Formula?
  • Calculator for Loan Repayment
  • Loan Calculator
  • Principal Amount of Loan
0 Shares
Share
Tweet
Share
Financial Modeling Course (with 15+ Projects)
  • 16 Courses
  • 15+ Projects
  • 90+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Amortized Loan Formula Excel Template

Coursera IPO Financial Model & Valuation Free Download