Afia Kona
If you are looking for Loan Amortization Schedule Excel with Moratorium Period, you have come to the right place. Here, we will walk you through 4 easy steps to do the task smoothly.
Download Practice Workbook
What Is Loan Amortization Schedule and Moratorium Period?
Loan Amortization is a debt or loan that pays off in installments all round of the whole term of the loan. It requires circular monthly payments. Every month, a part of the payment proceeds to the loan’s principal, and the other parts of it proceed to interest. Loan Amortization is also known as an installment loan. Equal monthly payments are required for a full Amortization Loan.
A Moratorium Period is a period in a loan term that the borrower can hold up or omit a portion, or the whole EMI (Equated Monthly Installment). It also means that the course of payments from the loan bearer to the bank or lender is postponed until the moratorium ends.
Steps to Create Loan Amortization Schedule with Moratorium Period in Excel
The following Loan Details table contains loan properties. We will use this table to create a loan amortization schedule Excel with moratorium period. To do so, we will go through 4 easy steps. Here, we used Excel 365. You can use any available Excel version.
Step-1: Calculating Moratorium Period to Create Loan Amortization Schedule
First, we will calculate the EMI((Equated Monthly Installment) by using the IF and PMT functions.
Steps:
- In the beginning, we will type the following formula in cell C14.
=IF(B14="Moratorium Period",0,PMT($D$8/$D$7,$D$7*$D$6,$D$5))
Formula Breakdown
- PMT($D$8/$D$7,$D$7*$D$6,$D$5) → depending on fixed payment and a fixed interest rate the PMT function will return payment of the loan amount.
- $D$8/$D$7→ is the rate that will calculate Interest Rate.
- $D$7*$D$6 → is npr, it denotes total periods of payments for the loan.
- $D$5 → is pv that is the Loan Amount.
- PMT(10%/6,6*1,20,000) → Therefore, it becomes
- Output: -$3530
- IF(B14=”Moratorium Period”,0,PMT($D$8/$D$7,$D$7*$D$6,$D$5)) → the IF function makes a logical comparison between a value and what we expect.
- IF(B14=”Moratorium Period”,0,-$3530)) → returns 0 when the logical statement is True, otherwise it returns -$3530.
- Output: $0
- Explanation: Since the logical statement is True, the IF function returns $0 as EMI for Moratorium Period.
- After that, press ENTER. Then, you can see the result in cell C14.
- Next, we will drag down the formula with the Fill Handle tool.
As a result, you can see the complete EMI column.
Read More: Car Loan Amortization Schedule in Excel with Extra Payments
Step-2: Calculating Loan Interest
Next, we will calculate the Interest using the IF and IPMT functions.
Steps:
- First, we will type the following formula in cell D14.
=IF(B14="Moratorium Period",0,IPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5))
Formula Breakdown
- IPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5)→ depending on annual, fixed payment & fixed interest rate it will interest payment of annual investment.
- $D$8/$D$7→ is the rate, means Interest Rate.
- B14 is the period for which we want to find the interest and it has to be within the limit of 1 to nper.
- $D$7*$D$6 → is npr, which is received payments in total for the loan.
- $D$5 → is pv that is the Loan Amount.
- IPMT(10%/6,”Moratorium Period”,6*1,20,000) → Therefore, it becomes
- Output: #VALUE!
- Explanation: Here, “Moratorium Period” is the period, therefore, the IPMT function returns an error. This is because the period must be a numerical value.
- IF(B14=”Moritorium Period”,0,IPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5)) → the IF function makes a logical comparison between a value and what we expect.
- IF(B14=”Moratorium Period”,0,#VALUE!)) → returns 0 when the logical statement is True, otherwise it returns #VALUE!.
- Output: $0
- Explanation: Since the logical statement is True, the IF function returns $0 as Interest for the Moratorium Period.
- Afterward, press ENTER. Then, you can see the result in cell D14.
- Moreover, we will drag down the formula with the Fill Handle tool
Therefore, you can see the complete Interest column.
Read More: Excel Simple Interest Loan Calculator with Payment Schedule
Step-3: Calculating Principal Amount to Create Loan Amortization Schedule
At this point, we will calculate Principal by using the combination of IFERROR and PPMT functions.
Steps:
- Firstly, we will type the following formula in cell E14.
=IFERROR(PPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5),0)
Formula Breakdown
- PPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5) → it will return the payment on a principal based on the given values.
- $D$8/$D$7→ is the rate, means Interest Rate.
- B14 → is the period for which we want to find the interest and has to be within the limit of 1 to nper.
- $D$7*$D$6 → is npr, which is the received payments in total for the loan.
- $D$5 → is pv that is the Loan Amount.
- PPMT(10%/6,”Moratorium Period”,6*1,20,000) → Therefore, it becomes
- Output: #VALUE!
- Explanation: Here, “Moratorium Period” is the period, therefore, the PPMT function returns an error. This is because the period must be a numerical value.
- IFERROR(PPMT($D$8/$D$7,B14,$D$7*$D$6,$D$5),0) →the IFERROR function returns 0, when a function returns an error, otherwise, it returns the value of the function.
- IFERROR(PPMT(#VALUE!,0) →Therefore, this becomes
- Output: $0.00
- Explanation: Here, the PPMT function returns an error, as a result, the IFERROR function handles the error and returns Principal as $0.00 for Moratorium Period.
- Secondly, press ENTER. Then, you can see the result in cell E14.
- Finally, we will drag down the formula with the Fill Handle tool.
Then, you can see the complete Principal column.
Read More: Student Loan Payoff Calculator with Amortization Table in Excel
Step-4: Calculating Remaining Balance
Finally, we will calculate Balance by using the SUM function.
Steps:
- First of all, we will type the following formula in cell F14.
=SUM(F13,E14)
Formula Breakdown
- SUM(F13,E14) the SUM function adds values of the cells.
- SUM(20,000,0) Hence, it becomes
- Output: $20,000
- Explanation: Here, $20,000 is the Balance for Moratorium Period.
- At this point, press ENTER. Then, you can see the result in cell F14.
- Moreover, drag down the formula with the Fill Handle tool.
As a result, we can see the complete schedule for Loan Amortization with moratorium period.
As the Balance for the last installment is $0 in cell F20, the schedule is complete.
Practice Section
You can download the above Excel file to practice the explained methods.
Conclusion
Here, we tried to show you 4 steps for Loan Amortization Schedule Excel with Moratorium Period. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
Related Articles
- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- Excel Loan Calculator with Extra Payments (2 Examples)
- Home Loan EMI Calculator with Reducing Balance in Excel
- Create Home Loan Calculator in Excel Sheet with Prepayment Option
- Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
