# How to Calculate the Down Payment in Excel Using VLOOKUP – 5 Steps

### Step 1 – Create a Dataset

Create a purchase price range and enter the rate, the NPER, and the PMT.

### Step 2 – Find the PMT Using the VLOOKUP Function

To find the PMT for a purchase price of \$27000:

• Select I5.
• Enter the following formula.
`=VLOOKUP(I4,\$C\$5:\$D\$11,2,TRUE)`

Formula Breakdown

VLOOKUP(I4,\$C\$5:\$D\$11,2,TRUE):  \$27000 is the lookup value in I4. C5:D11 is assigned as table array The VLOOKUP function searches the lookup value in the table array. The column number to get the result is assigned. The PMT values are in the second column. True is set to an approximate match. For \$27000, the VLOOKUP function returns \$1000 PMT.

• Press Enter.

### Step 3 – Calculate the Rate Using the VLOOKUP Function

• Select I6.
• Enter the following formula.
`=VLOOKUP(I4,\$C\$5:\$E\$11,3,TRUE)`

Formula Breakdown

VLOOKUP(I4,\$C\$5:\$E\$11,3,TRUE): \$27000 is the lookup value in I4. E11. C5:E11 is assigned as table array The VLOOKUP function searches the lookup value in the table array. The column number to get the result is assigned. The Rate is in the third column. True is set to an approximate match. For \$27000, the VLOOKUP function returns a Rate of 8%.

• Press Enter.

### Step 4 – Calculate the NPER

• Select I7.
• Enter the following formula.
`=VLOOKUP(I4,\$C\$5:\$F\$11,4,TRUE)`

Formula Breakdown

VLOOKUP(I4,\$C\$5:\$F\$11,4,TRUE):  \$27000 is the lookup value in cell I4. C5:F11 is assigned as table array The VLOOKUP function searches the lookup value in the table array. The column number to get the result is assigned.The NPER values are in the fourth column. True is set to an approximate match. For \$27000, the VLOOKUP function returns the NPER value as 24.

• Press Enter.

### Step 5: Calculate the Down Payment

• Select I10.
• Enter the following formula.
`=``I4-PV(I6/12,I7,-I5)`

Formula Breakdown

I4-PV(I6/12,I7,-I5): The down payment amount is 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. You need to calculate the monthly rate dividing it by 12 and subtracting it from the purchase price.

• Press Enter.

• If you change the purchase price from \$27000 to \$48000, the down payment will change.

## Related Articles

<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF