Create Loan Amortization Schedule with Moratorium Period in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Loan Amortization Schedule Excel with Moratorium Period

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

Loan Amortization Schedule Excel with Moratorium Period

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)

Loan Amortization Schedule Excel with Moratorium Period

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.

Loan Amortization Schedule Excel with Moratorium Period

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.

Loan Amortization Schedule Excel with Moratorium Period

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.

Loan Amortization Schedule Excel with Moratorium Period


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

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo