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

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.

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

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

Download Practice Workbook

What Is a Chattel Mortage?

Steps to Make Chattel Mortgage Calculator in Excel

📌 Step 1: Calculate Monthly Payment

📌 Step 2: Obtain Total Payment

📌 Step 3: Compute Monthly Balance

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

Practice Section

Conclusion

Related Articles

Download Practice Workbook

Making Chattel Mortgage Calculator.xlsx

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.

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

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

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

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.

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

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

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

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

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

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.

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

  • 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) indicating the present value of the payments to be made in the future.
    • Output($5,982.51)

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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

Conclusion

In essence, this article shows simple stepsto 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.

Related Articles

  • How to Create Fixed Rate Mortgage Calculator in Excel
  • Amortization Schedule with Irregular Payments in Excel (3 Cases)
  • How to Create Offset Mortgage Calculator in Excel
How to Make Chattel Mortgage Calculator in Excel (with Alternative) (2024)

FAQs

What formula would you use in Excel to calculate a monthly mortgage payment? ›

To figure out how much you must pay on the mortgage each month, use the following formula: "= -PMT(Interest Rate/Payments per Year,Total Number of Payments,Loan Amount,0)".

Does Excel have a mortgage calculator function? ›

A mortgage calculator in Excel is not a built-in feature in Excel. But, we can make our mortgage calculator using some formulas. To make a mortgage calculator and calculate the amortization schedule, we need to create our categories column for all the types and data to be inserted.

What is the PMT function in Excel for mortgage? ›

The Excel PMT Function returns the regular and constant repayments for a loan or mortgage required to reduce the balance to zero, or an amount you specify. It's based on a constant interest rate.

How do I calculate monthly totals in Excel? ›

To sum values based on the month, when you have dates in a column, you can use SUMIFS or SUMPRODUCT. With SUMIFS, you can refer to dates that fall within a specific month of the year. And with the SUMPRODUCT, you can sum for a particular month irrespective of the year.

What is the function for loan calculation in Excel? ›

The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount. "PMT" stands for "payment", hence the function's name.

How do I calculate loan amount in Excel? ›

How to Calculate an Original Loan Amount in Excel. We can calculate an original loan amount by using the Present Value Function (PV) if we know the interest rate, periodic payment, and the given loan term. This function tells the present value of an investment.

Can I make my own amortization schedule? ›

You can build your own amortization schedule and include an extra payment each year to see how much that will affect the amount of time it takes to pay off the loan and lower the interest charges.

What is the easiest way to calculate a mortgage payment? ›

For example, if your interest rate is 6 percent, you would divide 0.06 by 12 to get a monthly rate of 0.005. You would then multiply this number by the amount of your loan to calculate your loan payment. If your loan amount is $100,000, you would multiply $100,000 by 0.005 for a monthly payment of $500.

What is the formula for monthly rate? ›

Calculating your monthly APR rate can be done in three steps: Step 1: Find your current APR and balance in your credit card statement. Step 2: Divide your current APR by 12 (for the twelve months of the year) to find your monthly periodic rate. Step 3: Multiply that number with the amount of your current balance.

How to calculate mortgage interest rate based on monthly payment? ›

If you have a 6 percent interest rate and you make monthly payments, you would divide 0.06 by 12 to get 0.005. Multiply that number by your remaining loan balance to find out how much you'll pay in interest that month.

How do I calculate loan percentage in Excel? ›

=PMT(rate,periods,-amount)
  1. nper – the number of monthly durations/periods.
  2. rate – Interest Rate per duration.
  3. pv – the initial loan amount.

How do I add a financial calculator to Excel? ›

To make your own financial calculator in Excel, start a new file or sheet and label fields for Rate, Nper, PMT, PV, and Type, and add some sample values. Choose the cell where you want the result for FV to go. Click Insert, then Function (or fx on the task bar) to open Insert Function window.

Does Microsoft Excel have an amortization schedule? ›

Does Excel have a loan amortization schedule? Yes, Excel has a simple loan amortization schedule template available.

References

Top Articles
Latest Posts
Article information

Author: Jamar Nader

Last Updated:

Views: 5706

Rating: 4.4 / 5 (55 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Jamar Nader

Birthday: 1995-02-28

Address: Apt. 536 6162 Reichel Greens, Port Zackaryside, CT 22682-9804

Phone: +9958384818317

Job: IT Representative

Hobby: Scrapbooking, Hiking, Hunting, Kite flying, Blacksmithing, Video gaming, Foraging

Introduction: My name is Jamar Nader, I am a fine, shiny, colorful, bright, nice, perfect, curious person who loves writing and wants to share my knowledge and understanding with you.