Bhubon Costa
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.
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)
- Then, press Enter to get the Principal Loan.
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
- Therefore, see the Monthly Interest Rate of 0.00375.
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
- Press Enter to get the Interest Only Mortgage Duration of 120 months.
Step 4: Count the Rest Repay Period in Months
- As before, multiply the year by 12 to get the result in months.
=D8*12
- The result will show as 240 months for 20 years.
Step 5: Make a Column for the Months
- To make a serial of a total of 360 months, type 1 in a cell.
- Click on the Fill.
- Then, select the Series.
- Select the Columns.
- Set Step value to 1.
- Then, set the Stop value to 360.
- Finally, press Enter to get a serial of 360.
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
- Therefore, you will find the Interest Only for one month.
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)
- 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))
- 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).
- Finally, you will obtain the Interest Only Mortgage.
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
- As a result, you will get Zero (–) as a value, as you are just paying the Interest for first 120 months.
Step 9: Copy the Formula in the Payment Column
- Use the AutoFill to copy the same formula in each cell of the Payment Column.
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)
Step 11: AutoFill the Interest Paid Column
- Use the AutoFill to copy the same formula in each cell of the Interest Paid Column.
Step 12: AutoFill the Principal Paid Column
- Apply the AutoFill to copy the same formula in each cell of the Principal Paid Column.
- As a result, the Interest Only $3000 will be evaluated for 120 months as per the condition.
- Additionally, the monthly payment of $5061.20 will be paid for the rest of 240 months (121 to 360).
- Finally, the monthly Interest Only Mortgage payment of $3000 and the monthly mortgage payment of $5061.20 are shown in the image below.
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
- Loan Amortization Schedule with Variable Interest Rate in Excel
- How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
- Excel Interest Only Amortization Schedule with Balloon Payment Calculator
- How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods)
- Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
- Creation of a Mortgage Calculator with Taxes and Insurance in Excel
