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

Get FREE Advanced Excel Exercises with Solutions!

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.

Chattel mortgage calculator in Excel

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.


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.

Loan and Item Information of the dataset

  • 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 the rate argument that refers to the interest rate of the loan. Next, $D$6*$D$7 is the nper argument, which signifies the total number of payments, and $D$8 is the pv argument indicating the present value of the future payments.
    • Output$6,576.26

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

Using PMT function to calculate Payment

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.

Populating Payment values and formatting as currency

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

Entering extra payment values

  • 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”.

Obtaining Total payment by adding payment and extra payment

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 the rate argument that refers to the interest rate of the loan. Next, the B14 cell reference is the per argument indicating the period for which to find the interest. Later, $D$6*$D$7 is the nper argument signifying the total number of payments. Lastly, $D$8 is the pv argument indicating the present value of the future payments.
    • Output($593.75)

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

Using IPMT function to calculate interest payment to make chattel mortgage calculator excel

  • 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 the rate argument referring to the interest rate, while the B14 cell reference is the per argument indicating the interest period. Lastly, $D$6*$D$7 (nper argument) represents the total number of payments, and $D$8 (pv argument) indicates the present value of the payments to be made in the future.
    • Output($5,982.51)

Using PPMT function to calculate principal payment to make chattel mortgage calculator excel

  • 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”.

Entering balance for the initial month to make chattel mortgage calculator excel

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.

Balance checking out for chattel mortgage calculator excel using PMT, IPMT, and PPMT 


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”.

Entering amount and formatting as currency

  • 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”.

Using formula to get interest payment

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”.

Entering total payment and applying formula to get principal payment

  • 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”.

obtaining amount for the second month

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

Using fill handle tool

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

Entering total payment for second month

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

Dragging fill handle tool to complete table for chattel mortgage calculator excel

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. 🙂

Amount left to pay after 12 months

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

Using goal seek in the data tab

  • 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.

Entering arguments for the goal seek feature to make chattel mortgage calculator excel

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.

utilizing goal seek feature to make chattel mortgage calculator excel

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.

Practice section for chattel mortgage calculator excel


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo