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.

**Table of Contents**hide

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