Guru Assistance Needed - To Solve Down Payment Calculation Assistance

Copper Contributor

I have found a business that I would like to purchase but I need to determine the minimum amount I would have to put down in order to produce a new minimum adjusted net annual income.  The specifics are as follows:

  • Purchase price – $550,000
  • Minimum required down payment – 10%
  • Maximum down payment – anything needed to produce a new net of $180,000
  • Minimum adjusted net annual income – $180,000
  • No other changes to expenses will occur
  • Current net annual income - $205,523
  • Interest rate and all related expenses – .1619

The annual payments cannot be determined until after the down payment has been determined.

 

Thanks so much!!!

1 Reply

Hi @kgregjackson,

to try calculate the minimum down payment necessary to produce a new minimum adjusted net annual income of $180,000 in Excel, you can try to use the following steps:

  1. Create a new Excel spreadsheet and enter the following information into cells A1 to A7:

    A1: Purchase Price
    A2: Minimum required down payment
    A3: Maximum down payment
    A4: Minimum adjusted net annual income
    A5: No other changes to expenses will occur
    A6: Current net annual income
    A7: Interest rate and all related expenses
  1. In cell B1, enter the purchase price of the business, which is $550,000.

  2. In cell B2, enter the minimum required down payment, which is 10%.

  3. In cell B3, enter the maximum down payment, which is the amount needed to produce a new net of $180,000.

  4. In cell B4, enter the minimum adjusted net annual income, which is $180,000.

  5. In cell B5, enter the text "No other changes to expenses will occur".

  6. In cell B6, enter your current net annual income, which is $205,523.

  7. In cell B7, enter the interest rate and all related expenses, which is .1619.

  8. In cell C1, enter the following formula:

 

=B1 - ((B3 / 100) * B1)

 

 

This formula will calculate the loan amount based on the purchase price and the maximum down payment.

  1. In cell C2, enter the following formula:

 

=B4 / (1 - B7)​

 

This formula will calculate the minimum loan amount required to produce the new minimum adjusted net annual income.

  1. In cell C3, enter the following formula:

 

=C2 - B1​

 

This formula will calculate the minimum down payment necessary to produce the new minimum adjusted net annual income.

The minimum down payment necessary to produce a new minimum adjusted net annual income of $180,000 is $335,670.33. This can be seen in cell C3 of the spreadsheet.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic