How to calculate a deposit or down payment in Excel

Join Excel List

Purchasing a car or a house is usually a pleasant experience for the buyer. However, most financial institutions require a deposit or down payment to be made towards the total purchase price.

In this week’s tip, we share how to calculate the deposit or down payment for a car. For instance, the cost of the car could be $15,000 to be repaid over four years at 3% per period. Furthermore, you may want to keep the monthly payment at $400 per month, but need to calculate the deposit. This is how it can be done.

Note: You are welcome to download the workbook to practice

Applies To: Microsoft® Excel®   2010 and 2013

1. We are going to use the following formula:

=Purchase Price-PV(Rate,Nper,-Pmt)

  • PV: calculates the loan amount.
  • The loan amount will be subtracted from the purchase price to get the deposit amount.
  • Rate: is the interest rate per period. It will be divided by 4 if it is per quarter or 12 if it’s per annum.
  • Nper: is the total number of payment periods in an investment, which will be 48(4*12).
  • Pmt: is the payment made each period.

2. Place the cursor in cell C6 and enter the formula below.

=C2-PV(C3/12,C4,-C5)

3. This will give you $3,071.48 as the deposit.

1. Excel On Steroids_CalculateDepositsorDownPayments

By using this method you can calculate the deposit for a car or house using an easy formula.


Sage © Sage South Africa Pty Ltd 2016 . All Rights Reserved.