Certainly, Microsoft Excel is a handy and popular tool for analyzing large sets of data. Now, if you are looking to calculate your **Chattel Mortgage**, then this article is the answer to your prayer! In fact, this article shows how to make a chattel mortgage calculator in Excel. In addition to that, we have added an alternative tool **(Goal Seek)** to perform the same task.

The following image tells what the Chattel Mortgage calculator is going to look like.

In this case, the above screenshot is an overview of the article, and in the following sections, you’ll explore the quarks and features regarding the **Chattel Mortgage** calculator.

**Table of Contents**hide

## What Is a Chattel Mortage?

First and foremost, let’s get ourselves up to speed on what a **Chattel Mortgage **is.

Simply put, a **Chattel Mortgage** is used to buy portable items, including automobiles, machinery, fabricated houses, etc.

In general, **Chattel Mortgage** differs from a traditional mortgage in the sense that the moneylender owns the asset until the debt is settled. In contrast, in a standard mortgage, the lender doesn’t own the assets but can take over their ownership as collateral for the outstanding debt.

## How to Make Chattel Mortgage Calculator in Excel (with Alternative)

Henceforth, without further delay, let’s discuss each method on how to make a **Chattel Mortgage** calculator in Excel with the necessary illustrations. As a note, we’ve used the *Microsoft Excel 365* version; you may use any other version at your convenience.

First of all, let’s use the built-in **PMT**, **IPMT**, and **PPMT** functions to construct a **Chattel Mortgage** calculator in Excel. Here, the **PMT** function calculates the periodic (monthly) payments, while the **IPMT** function returns the accrued interest of the loan. Lastly, the **PPMT** function gives the principal payments for the loan.

### 📌 Step 1: Calculate Monthly Payment

- First, enter the
*“Loan Information”*and*“Item Information”*into the tables as shown in the image below.

- Next, go to the
**D10**cell >> enter the formula below >> press the**ENTER**key.

`=-PMT($D$5/$D$7,$D$6*$D$7,$D$8)`

Here, **D5**, **D6**, **D7**, and **D8** cells represent the *“Annual Interest Rate”*, *“Loan Tenure”*, *“Yearly Payments”*, and *“Loan Amount”*. In addition, the leading **Minus** (**–**) sign makes the value positive.

**Formula Breakdown**

**PMT($D$5/$D$7,$D$6*$D$7,$D$8)**→ calculates the payment for a loan based on constant payments and a constant interest rate. Here,**$D$5/$D$7**is theargument that refers to the interest rate of the loan. Next,*rate***$D$6*$D$7**is theargument, which signifies the total number of payments, and*nper***$D$8**is theargument indicating the present value of the future payments.*pv***Output**→**$6,576.26**

📃 *Note:**Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

**Read More: **Biweekly Mortgage Calculator with Extra Payments in Excel

### 📌 Step 2: Obtain Total Payment

- Second, move to the
**C14**cell >> type the**D10**cell reference >> click**ENTER**>> change the number format to**Currency**.

`=$D$10`

In this case, the **D10** cell points to the loan *“Payment”* value.

- Then, add
*“Extra Payment”*, for instance, we’ve chosen to add*“$500”*,*“$1500”*and*“$750”*USD on the*“3rd”*,*“7th”*, and*“10th”*months respectively.

- Following this, compute the
*“Total Payment”*by adding the values in the*“Payment”*and*“Extra Payment”*columns.

`=C14+D14`

For example, the **C14** and **D14** cells represent the *“Payment”* and *“Extra Payment”* values for *“Month 1”*.

**Read More: **Mortgage Calculator with Extra Payments and Lump Sum in Excel

### 📌 Step 3: Compute Monthly Balance

- Third, navigate to the
**F14**cell >> copy and paste the expression below >> hit**ENTER**.

`=IPMT(D$5/D$7,B14,D$6*D$7,D$8)`

In this scenario, the **B14** cell refers to *“Month”* number *“1”*.

**Formula Breakdown**

**IPMT(D$5/D$7,B14,D$6*D$7,D$8)**→ returns interest payments based on periodic, constant payments and a constant interest rate. Here,**$D$5/$D$7**is theargument that refers to the interest rate of the loan. Next, the*rate***B14**cell reference is theargument indicating the period for which to find the interest. Later,*per***$D$6*$D$7**is theargument signifying the total number of payments. Lastly,*nper***$D$8**is theargument indicating the present value of the future payments.*pv***Output**→**($593.75)**

📃 *Note:**The IPMT function formats the numbers in Accounting format, so the Negative numbers appear in red color surrounded by parentheses.*

- From this point, jump to the
**G14**cell to get the*“Principal”*payment values.

`=PPMT(D$5/D$7,B14,D$6*D$7,D$8)`

**Formula Breakdown**

**PPMT(D$5/D$7,B14,D$6*D$7,D$8)**→ returns payment on the principal for a given investment based on periodic, constant payments and a constant interest rate. Here,**$D$5/$D$7**is theargument referring to the interest rate, while the*rate***B14**cell reference is theargument indicating the interest period. Lastly,*per***$D$6*$D$7**(argument) represents the total number of payments, and*nper***$D$8**(argument) indicates the present value of the payments to be made in the future.*pv***Output**→**($5,982.51)**

- In turn, enter the
**D8**cell address into the**H13**cell.

`=$D$8`

On this occasion, the **D8** cell points to the initial *“Loan Amount”* of *“$75,000”*.

Lastly, type the following equation into the **H14** cell >> press **ENTER** >> drag the **Fill Handle **tool to copy it to the cells below.

`=H13+G14-D14`

In this formula, the **D14**, **G14**, and **H13** cells point to the *“Payment”*, *“Principal”*, and *“Balance”* values respectively.

## Excel Goal Seek Feature: An Alternative Approach to Calculate Chattel Mortgage Payment

Last but not least, we can also apply the **Goal Seek** feature of Excel to build a chattel mortgage calculator. So, just follow the steps shown below.

📌 __Steps__**:**

- Initially, type the cell reference for the
*“Loan Amount”*in the**C16**cell >> apply the**Currency**number formatting.

`=D13`

Here, the **D13** cell refers to the *“Loan Amount”* of *“$125,000”*.

- Afterward, proceed to the
**D16**cell >> insert the expression into the**Formula Bar**>> hit the**ENTER**key.

`=(C16*$D$10)/$D$12`

For instance, the **C16**, **D10**, and **D12** cells correspond to the *“Amount”*, *“Annual Interest Rate”*, and *“Yearly Payments”*.

Now, let’s suppose we want to deposit *“$7,500”* of monthly *“Total Payment” *entered in the **F16** cell.

- Later, calculate the
*“Principal Payment”*using the following equation.

`=F16-D16`

In this case, the **F16** and **D16** cells signify the *“Total Payment”* and *“Interest”*.

- Hence, apply the formula given below to obtain the
*“Amount”*for*“Month 2”*.

`=C16-E16`

Now, the **C16** and **E16** cells refer to the *“Amount”* and *“Principal Payment”*.

- Not long after, implement the
**Fill Handle**tool to copy the formula to the**D17**and**E17**cells.

- After that, type the following cell address in the
**F17**cell >> click on**ENTER**.

- Eventually, choose the
**C17:F17**cells >> copy the formulas to populate the cells below.

However, this presents a dilemma; that is to say, at the end of the 12th month, we still owe *“$47,031.65”* to the creditor. Therefore, our monthly payment of *“$7500”* is too low to pay off the debt within the 12-month time frame.

Luckily, Excel has a nifty trick up its sleeve to solve this problem, so follow along. 🙂

- At this time, navigate to the
**Data**tab >> click the**What-If Analysis**drop-down >> choose**Goal Seek**.

- Now, in the
**Goal Seek**wizard, make the selections shown below.- First, in the
**Set cell**field, select the**C27**cell that refers to the*“Amount”*for the last month. - Second, enter
**Zero**(**0**) for the**To value**field. - Third, click on the
**F16**cell to set the**By changing cell**option.

- First, in the

Eventually, the **Goal Seek** feature should return the final output shown in the screenshot below.

In short, our monthly payment has to increase to *“$11,678.52”* if we want to pay off the debt within 1 month.

**Read More: **Early Mortgage Payoff Calculator in Excel

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

In essence, this article shows simple steps to make a chattel mortgage calculator in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

## Related Articles

- How to Create Fixed Rate Mortgage Calculator in Excel
- How to Create Offset Mortgage Calculator in Excel
- Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
- How to Create Reverse Mortgage Calculator in Excel
- Calculator for Effective Interest Method of Amortization
- Creation of a Mortgage Calculator with Taxes and Insurance in Excel
- Interest Only Mortgage Calculator with Excel Formula