Can anyone help me. I'm after an amortization schedule for loans that has a daily compound interest added with a monthly repayment. I've calculated the monthly repayment but I can't find an excel formula that provides me with a breakdown of principal and interest on these monthly payments. I've tried everything to create my own but I can create one for monthly compound but not for daily.Many thanks
Balloon Loan Amortization Schedule Template. Use this Excel amortization schedule template to determine balloon payments. A balloon payment is when you schedule payments so that your loan will be paid off in one large chunk at the end, after a series of smaller payments are made to reduce the principal. The preferred method of getting at this data is to create a visual representation of it using a compound interest table template. Using the Compound Interest Table Template. In order to accurately calculate the earnings accrued over a two-year period for a $10,000 investment according to different rates of interest compounded monthly, you would.
Thanks for replying - is it therefore impossible to use the ppmt function in excel using a daily compound rate? The formula is PPMT(rate, per, nper, pv, [fv], [type]) so for the rate I put in 8.5%/365. This worked for monthly i.e. 8.5%/12 but gives me different amounts for principal and interest (ipmt i did the same for) as to what I know the monthly payment is when trying to calculate using a daily compound formula. As you can probably tell, I'm not a whizz on Excel so unsure of how I can utilise this PPMT function with the formula you have given me? Any further advice would be much appreciated
JoeTaxpayer♦
KarenKaren
1 Answer
Just convert the 'daily compound interest' into 'effective monthly interest'.
If Nominal Interest is 8.5% per per annum compounded daily,
each day is 8.5% / 360, so each month is ((1 + (8.5% / 360))^30) - 1.
So the effective interest over 1 month is ((1 + (8.5% / 360))^30) - 1,
and the nominal interest per annum is ((((1 + (8.5% / 360))^30) - 1) * 12) compounded monthly.
Edit: You can just put ((1 + (8.5% / 360))^30) - 1 = 0.0071076374384 into
rate
of PPMT or IPMTbase64base64
Not the answer you're looking for? Browse other questions tagged compound-interest or ask your own question.
Amortization Formula(Table of Contents)
Amortization Formula
Amortization refers to paying off debt amount on periodically over time till loan principle reduces to zero. Amount paid monthly is known as EMI which is equated monthly installment. EMI has both principal and interest component in it which is calculated by amortization formula. Amortization calculation depends on the principle, the rate of interest and time period of the loan. Amortization can be done manually or by excel formula for both are different.
Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others
Now, let us see how to calculate Amortization manually.
Monthly payment i.e. can be calculated by below formula:-
And the formula for interest is as follows:-
Where,
- P = Principle
- r= Rate of interest
- t = Time in terms of year
- n = Monthly payment in a year
- I = Interest
- ƥ = Monthly Payment or EMI amount
Example of Amortization Formula
Now, let’s see an example to understand the calculation.
You can download this Amortization Formula Excel Template here – Amortization Formula Excel Template
A salaried person took home loan from a bank of $100,000 at the rate of interest of 10% for a period of 20 years. Now, we have to calculate the EMI amount and interest component paid to the bank.
- P = $100,000
- r= 10% i.e 0.1
- t = 20
- n = 12
Amortization is Calculated Using Below formula:
- ƥ = rP / n * [1-(1+r/n)-nt]
- ƥ = 0.1 * 100,000 / 12 * [1-(1+0.1/12)-12*20]
- ƥ = 965.0216
And now, to calculate interest paid we will put value in interest formula.
- I = nƥt – P
- I = 12*965.0216*20 – 100,000
- I = $131,605.2
So, interest paid on loan is $131,605.2.
Significance and Use of Amortization Formula
There are many uses of amortization they are as follows:-
- It helps lender as well as the borrower for systematic repayment.
- There are very fewer chances of error.
- The borrower can check its principal amount outstanding at any point in time.
- It creates transparency between borrower and lender.
Amortization is calculated for loan repayment. Amortization is used in Personal loan, Home loan, Auto loan repayment schedule preparation. It gives deep details from starting till maturity of the loan. If any borrower does part payment his amortization schedule changes and effect of same is visible on EMI or tenure that means borrower can request for tenure change where EMI tenure will reduce and his EMI amount will be same or he can request for the change in EMI where EMI amount will reduce and tenure will be the same. In loans, more prepayment is done will result in less interest as principal balance will reduce. By using amortization calculation became very easy even in the above scenario.
Amortization Calculator
You can use the following Amortization Formula Calculator
Amortization Formula = |
| |||
|
Amortization Formula in Excel (With Excel Template)
Now, let us see how amortization can be calculated by excel.
Finance for Non Finance Managers Training Course7 Online Courses | 25+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (1,000 ratings)
4.5 (1,000 ratings)
Related Courses
Investment Banking CourseFinancial Modeling CourseA couple took an auto loan from a bank of $10,000 at the rate of interest of 10% for the period of 2 years. Now, we have to calculate EMI amount for the same.
Amortization in excel is Calculated Using Below formula:
= PMT(Rate,nper,pv)
In excel one can use below formula to calculate amortization value:-
- For calculation of interest paid during a specific period, we will use below formula.
=ISPMT(Rate,per,nper,pv)
- To calculate the amount of payment in a period below formula is used.
= PMT(Rate,nper,pv)
- To calculate a number of payment below formula is used.
= NPER(Rate,pmt,pv)
- To calculate cumulative interest payment for period n1 through n2.
=CUMIPMT(rate,nper,pv,n1,n2,0)
- To calculate cumulative principle payment for period n1 through n2.
=CUMIPRINC(rate,nper,pv,n1,n2,0)
- To calculate principle paid in an EMI below formula is used.
=PPMT(rate,per,nper,pv)
Where,
- pv = Present value of loan
- pmt = Payment per period
- nper = Number of payment period
- rate = Rate of interest
Through the above formula repayment schedule for a loan over a period is prepared which is known as amortization schedule.
Below are steps to prepare amortization schedule in excel.
- Put input of formula in a standard format.
Principle | $200,000 |
Rate of Interest | 9% |
Tenure(In years) | 10 |
- Plot table for an amortization schedule. In zero month column put balance as $200,000 and then put 1, 2, 3 and so on till last month of EMI in the month field.
Month | EMI | Principle | Interest | Balance |
- Calculate EMI with below formula:-
= PMT(Rate,nper,pv)
- Calculate principle with below formula:-
![Quarterly Compound Interest Amortization Workbook Template Free Quarterly Compound Interest Amortization Workbook Template Free](/uploads/1/2/5/8/125840132/709567791.png)
=PPMT(rate,per,nper,pv)
- Now, interest will be:-
Interest = EMI – Principle
- Balance will be previous balance minus principle.
Balance = Previous Balance – Principle
- Repeat the same till last month and we will get amortization schedule.
Now, we will see an example to prepare amortization schedule.
A person has taken the auto loan of $200,000 with the rate of interest 9% for the tenure of 3 years and he wants to prepare his amortization schedule.
Using the above formulas in excel he gets amortization schedule.
- Put input of formula in a standard format.
- Plot table for the amortization schedule. In zero month column put balance as $200,000 and then put 1, 2, 3 and so on till last month of EMI in the month field.
- Calculate EMI with below formula:-
- Calculate principle with below formula:-
- Now, interest will be:-
- The balance will be previous balance minus principle.
- Repeat the same till last month and he will get below amortization schedule.
Month | EMI | Principle | Interest | Balance |
0 | 200,000 | |||
1 | 6,360 | 4,860 | 1,500 | 195,140 |
2 | 6,360 | 4,896 | 1,464 | 190,244 |
3 | 6,360 | 4,933 | 1,427 | 185,311 |
4 | 6,360 | 4,970 | 1,390 | 180,340 |
5 | 6,360 | 5,007 | 1,353 | 175,333 |
6 | 6,360 | 5,045 | 1,315 | 170,288 |
7 | 6,360 | 5,083 | 1,277 | 165,205 |
8 | 6,360 | 5,121 | 1,239 | 160,084 |
9 | 6,360 | 5,159 | 1,201 | 154,925 |
10 | 6,360 | 5,198 | 1,162 | 149,727 |
11 | 6,360 | 5,237 | 1,123 | 144,490 |
12 | 6,360 | 5,276 | 1,084 | 139,214 |
13 | 6,360 | 5,316 | 1,044 | 133,898 |
14 | 6,360 | 5,356 | 1,004 | 128,542 |
15 | 6,360 | 5,396 | 964 | 123,146 |
16 | 6,360 | 5,436 | 924 | 117,710 |
17 | 6,360 | 5,477 | 883 | 112,233 |
18 | 6,360 | 5,518 | 842 | 106,715 |
19 | 6,360 | 5,560 | 800 | 101,155 |
20 | 6,360 | 5,601 | 759 | 95,554 |
21 | 6,360 | 5,643 | 717 | 89,911 |
22 | 6,360 | 5,686 | 674 | 84,225 |
23 | 6,360 | 5,728 | 632 | 78,497 |
24 | 6,360 | 5,771 | 589 | 72,725 |
25 | 6,360 | 5,815 | 545 | 66,911 |
26 | 6,360 | 5,858 | 502 | 61,053 |
27 | 6,360 | 5,902 | 458 | 55,151 |
28 | 6,360 | 5,946 | 414 | 49,204 |
29 | 6,360 | 5,991 | 369 | 43,214 |
30 | 6,360 | 6,036 | 324 | 37,178 |
31 | 6,360 | 6,081 | 279 | 31,097 |
32 | 6,360 | 6,127 | 233 | 24,970 |
33 | 6,360 | 6,173 | 187 | 18,797 |
34 | 6,360 | 6,219 | 141 | 12,578 |
35 | 6,360 | 6,266 | 94 | 6,313 |
36 | 6,360 | 6,313 | 47 | 0 |
Amortization Schedule
Amortization schedule helps one to know when he has to pay EMI against his loan and what is the EMI which he needs to pay, how much interest he has to pay on his loan, what is the principal outstanding of the loan. It is a very systematic and easy way to track repayment of the loan.
Amortization ends when the loan is matured and the principle balance is zero. If the amount is not recovered from borrower then interest accrued will be added to the outstanding amount which leads to an increase in the principle of the loan and this is known as negative amortization.
Recommended Articles
This has been a guide to an Amortization formula. Here we discuss its uses along with practical examples. We also provide you with Amortization Calculator with downloadable excel template. You may also look at the following articles to learn more –
Financial Analyst Course- All in One Bundle
250+ Online Courses
1000+ Hours
Verifiable Certificates
Lifetime Access