How to Calculate the Down Payment in Excel Using VLOOKUP

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.