Eshrak Kader
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.
Download Practice Workbook
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.
Steps to Make Chattel Mortgage Calculator in Excel
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.
- 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.
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”.
- 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.
- 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)
- 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) indicating the present value of the payments to be made in the future.
- 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.
Read More: Mortgage payoff calculator with extra principal payment (Free Template)
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.
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 (3 Practical Examples)
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.
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. Lastly, visit ExcelDemy for many more articles like this.
