Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)

Get FREE Advanced Excel Exercises with Solutions!

When you take out a loan to buy something and wish to return it over time with monthly payments, you may need to know the amount of the monthly payment. In financial words, the Interest Only Mortgage indicates that you will first pay only interest for a certain period of time before repaying the original loan plus interest on a monthly basis. In this tutorial, we will show you how to use the formula for the Interest Only Mortgage calculator in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Basic Concepts to Interest Only Mortgage Calculator Formula in Excel

Interest Only Mortgage means that you will pay only the interest for e certain period say for 10 years on a monthly basis. After the end of the period, you will repay the Principal Loan with interest on a monthly basis.

A sample data set is represented to understand it better. In our data set, we want to buy a home with a price of $1,000,000. We have made a down payment of about 20%. So, the Principal Loan is now $800,000. The Interest rate is 4.5% yearly.

The Interest Only period is 10 years or 120 months. And, 20 years is the period to repay the Principal Loan with the Interest.

Sample Data


12 Easy Steps to Use Formula for Interest Only Mortgage Calculator in Excel

In the following sections, we will utilize 12 steps to apply the formulas to calculate the Interest Only Mortgage. Firstly, we will calculate the Only Monthly Interest Payments. Later on, the monthly payment for the Principal Loan is added with its Interest.

Step 1: Calculate the Principal Loan

  • After making a 20% down payment, the 80% is the Principal Loan. Type the following formula to calculate the Principal Loan.
=((100%-D3)*D2)

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Then, press Enter to get the Principal Loan.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

Read More: How to Use Formula for Mortgage Principal and Interest in Excel


Step 2: Calculate the Monthly Interest

  • As the yearly interest is 4.50%, divide it by 12 to get the monthly Interest using the following formula.
=D6/12

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Therefore, see the Monthly Interest Rate of 0.00375.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

Read More: Mortgage Calculations with Excel Formula (5 Examples)


Step 3: Calculate the Interest Only Duration in Months

  • To convert years to months, just multiply by 12.
=D7*12

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Press Enter to get the Interest Only Mortgage Duration of 120 months.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel


Step 4: Count the Rest Repay Period in Months

  • As before, multiply the year by 12 to get the result in months.
=D8*12

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • The result will show as 240 months for 20 years.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel


Step 5: Make a Column for the Months

  • To make a serial of a total of 360 months, type 1 in a cell.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Click on the Fill.
  • Then, select the Series.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Select the Columns.
  • Set Step value to 1.
  • Then, set the Stop value to 360.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Finally, press Enter to get a serial of 360.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel


Step 6: Insert a Formula to Calculate the Monthly Interest Only Mortgage Calculator in Excel 

  • For calculating the monthly interest, type the following formula.
=$D$4*$E$6

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Therefore, you will find the Interest Only for one month.

Steps to Use Formula for Interest Only Mortgage Calculator in Excel


Step 7: Apply a Formula to Calculate the Monthly Payment for the Interest Only Mortgage Calculator in Excel 

  • To apply a condition for the Interest Only Period (120 months), write the following formula.
=IF(B11<=$E$7,D11)

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • For the value_if_false argument (months > 120), type the formula of the PMT function.
=IF(B11<=$E$7,D11,-PMT($E$6,$E$8,$D$4))

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • In the PMT function, the rate argument is set to cell E6 (Interest Per Month).
  • The nper argument is the number of periods set to cell E8.
  • The pv argument indicates the present value is set to cell D4 (Principal Loan Amount).

Steps to Use Formula for Interest Only Mortgage Calculator in Excel

  • Finally, you will obtain the Interest Only Mortgage.

Sample Data


Step 8: Type a Formula to Count the Monthly Principal Loan Paid for the Interest Only Mortgage Calculator in Excel 

  • Evaluate the Principal Loan paid per month by applying the following formula.
=C11-D11

Sample Data

  • As a result, you will get Zero (–) as a value, as you are just paying the Interest for first 120 months.

Sample Data


Step 9: Copy the Formula in the Payment Column

  • Use the AutoFill to copy the same formula in each cell of the Payment Column.

Sample Data


Step 10: Use the IF Function to Apply the Condition

  • As we want to calculate the Interest Only for 120 months, use the IF function to apply the condition.
=IF([@Month]<=$E$7,$D$4*$E$6,0)

Sample Data


Step 11: AutoFill the Interest Paid Column

  • Use the AutoFill to copy the same formula in each cell of the Interest Paid Column.

Sample Data


Step 12: AutoFill the Principal Paid Column

  • Apply the AutoFill to copy the same formula in each cell of the Principal Paid Column.

Sample Data

  • As a result, the Interest Only $3000 will be evaluated for 120 months as per the condition.

Sample Data

  • Additionally, the monthly payment of $5061.20 will be paid for the rest of 240 months (121 to 360).

Sample Data

  • Finally, the monthly Interest Only Mortgage payment of $3000 and the monthly mortgage payment of $5061.20 are shown in the image below.

Sample Data


Conclusion

Finally, I hope you now understand how to use the formula for the Interest Only Mortgage calculator in Excel. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.


Related Articles

 

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo