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**