When you buy an expensive thing, you may have paid some amount in the initial stage and will spend the rest in installments. That initial amount is known as a down payment. This article will show you how to calculate a down payment in Excel using the VLOOKUP function. I think you find this article informative and gain a lot of knowledge regarding VLOOKUP and the down payment.
What Is Down Payment?
A down payment can be defined as the amount of money paid in the early stage of buying an expensive product. For the rest of the money, people tend to take loans from banks and pay them in installments. The higher the down payment, the less the amount of money, you need to borrow from the banks, and at the same time, your monthly payment will be less. Depending on the type of products, you may have to pay 0% to 50% down payment. There are several benefits to having a large down payment.
The first major benefit of having a large down payment is that you have to borrow a lesser amount of money from banks or other financial organizations. The less amount of money means you have to pay lower interest. Secondly, the less amount of money borrowed means you have to pay a small amount of money payment compared to a lower down payment. Thirdly, a larger down payment helps you to avoid the payment of private mortgage insurance which repays your lender if you don’t pay your loan payments. If your down payment is higher than 20% of the purchase price, you don’t need to buy private mortgage insurance.
How to Calculate Down Payment in Excel Using VLOOKUP: Step-by-Step Procedures
To calculate a down payment in Excel using the VLOOKUP function, we would like to show step-by-step procedures through which you can have a proper overview. Before anything, you need to create a purchase price range and along with this, you have to put the values of NPER, PMT, and RATE. After that, for a given purchase price, we will find out the values of NPER, PMT, and RATE using the VLOOKUP function. Finally, calculate the down payment value. To understand this, you need to follow the steps carefully. ‘
Step 1: Create Dataset
Firstly, we would like to create a purchase price range. Under these ranges, we put different values of rate, NPER, and PMT. Using these ranges, we will find out values for a certain purchase price. Finally, calculate the required down payment.
Step 2: Find PMT Using VLOOKUP
In this step, we would like to find PMT using the VLOOKUP function. First, we put a specific purchase price of $27000. Then, we would like to calculate the PMT. To calculate this, follow the steps.
- First, select cell I5.
- Then, write down the following formula.
=VLOOKUP(I4,$C$5:$D$11,2,TRUE)
🔎 Breakdown of the Formula
VLOOKUP(I4,$C$5:$D$11,2,TRUE): The VLOOKUP function returns the value of a given lookup value in a certain table array. Here, the assigned purchase price of $27000 is the lookup value which is in cell I4. Then, assign the table array from cell C5 to cell D11. The VLOOKUP function will search the lookup value in that given table array. Then, assign the column number of the given array from where you would like to get the result. The PMT values are in the second column. Finally, set True to get an approximate match. For $27000, the VLOOKUP function returns $1000 PMT.
- After that, press Enter to apply the formula.
Read More: How to Calculate Auto Loan Payment in Excel
Step 3: Estimate Rate Using VLOOKUP
In the third step, we would like to find the rate using the purchase price as a given lookup value. Follow the steps.
- First, select cell I6.
- Then, write down the following formula in the formula box.
=VLOOKUP(I4,$C$5:$E$11,3,TRUE)
🔎 Breakdown of the Formula
VLOOKUP(I4,$C$5:$E$11,3,TRUE): The VLOOKUP function returns the value of a given lookup value in a certain table array. Here, the assigned purchase price of $27000 is the lookup value which is in cell I4. Then, assign the table array from cell C5 to cell E11. The VLOOKUP function will search the lookup value in that given table array. Then, assign the column number of the given array from where you would like to get the result. The Rate is in the third column. Finally, set True to get an approximate match. For $27000, the VLOOKUP function returns a Rate of 8%.
- After that, press Enter to apply the formula.
Step 4: Calculate NPER
Then, we would like to find out the NPER values using the VLOOKUP function. To calculate the down payment in Excel, the total number of payment periods is a must. Follow the steps.
- First, select cell I7.
- Then, write down the following formula in the formula box.
=VLOOKUP(I4,$C$5:$F$11,4,TRUE)
🔎 Breakdown of the Formula
VLOOKUP(I4,$C$5:$F$11,4,TRUE): The VLOOKUP function returns the value of a given lookup value in a certain table array. Here, the assigned purchase price of $27000 is the lookup value which is in cell I4. Then, assign the table array from cell C5 to cell F11. The VLOOKUP function will search the lookup value in that given table array. Then, assign the column number of the given array from where you would like to get the result. The NPER values are in the fourth column. Finally, set True to get an approximate match. For $27000, the VLOOKUP function returns the NPER value as 24.
- After that, press Enter to apply the formula.
Step 5: Calculate Down Payment
After finding PMT. Rate and NPER for a specific purchase price, we have to turn our attention toward the down payment. Using the values of  PMT. Rate and NPER, we would like to calculate the loan amount utilizing the PV function. Then, subtract it from the purchase price and you will get the required down payment. Follow the steps.
- First, select cell I10.
- Then, write down the following formula.
=I4-PV(I6/12,I7,-I5)
🔎 Breakdown of the Formula
I4-PV(I6/12,I7,-I5): The down payment amount can be calculated by subtracting the value of the PV function from the purchase price. The PV function returns the loan amount or present value using Rate, NPER, and PMT. We need to calculate the rate monthly by dividing it by 12. Finally, subtract it from the purchase price and you will get your desired down payment.
- After that, press Enter to apply the formula.
- Now, if you change the purchase price from $27000 to $48000, the down payment will change accordingly.
Read More: How to Calculate Monthly Payment on a Loan in Excel
Things to Remember
- Before using the VLOOKUP function, you need to have a purchase price range table otherwise, there is no use for the VLOOKUPÂ function.
- While using the VLOOKUP function, you need to assign the proper table array. Otherwise, it will give an error.
Download Practice Workbook
Download the practice workbook below.
Conclusion
To calculate a down payment in Excel using the VLOOKUP function, you have shown step-by-step procedures through which we can have a clear idea. Using the VLOOKUP function, we calculated the total number of periods, payments for each period, and the interest rate. I think we covered all possible areas regarding the down payment using the VLOOKUP function. If you have further questions, feel free to ask in the comment box.
