Amortization Question

[Bob B.]

I have an offer to buy a house I have for sale on land contract from my current renter. We'll enter a 3 year land contract with a baloon due at the end of the three years.

I currently owe $106,421 on the house. At the end of the three years, I project that I will owe $98,690.

The initial offer on the house s $95,000. I will present a counter offer. My ideal counter offer will set up an initial house value and payment structure such that at the end of the three years, the buyers owe us approximatley what we will owe the bank, give or take a few hundred dollars. May not happen, but that is my ideal.

More information: We currently collect $725 per month in rent. In previous communications with the renter, I have assured her that her monthy land contract payment would be equal to her rent payment. When we enter the land contract, she will be responsible for taxes, $180/mo. and we will be responsible for insurance. Subtracting $180 from $725 rent = $545/mo. contract payment, to keep her total monthly payments equal to rent payments.


I've played around with an amortization calculator, and have come up with the following numbes that work: $110,300 for the house, minus 5.9% down payment ($6500) = 103,800 initial amount subject to amortization. 12 payments per year, 4.8063% interest, $545 monthly payment amortized over 30 years equals $98,656 due at the end of the land contract.

I would like to set the initial house value lower, and increase the interest rate such that the amount due at the end of the contract is approximately equal to that figured above. My mortgage is at 6.35%, so I wouln't feel comfortable going more than 6.5%. Also, I wouln't feel comfortable going much beyond a 40 year amortization.

Can anybody guide me to a calculator or a method so I'm not randomly entering numbers for the next half day?

Thanks.

[jpg7n16]

How good are you with excel?

Given the figures you quoted, I'm getting an offering price of $110,200 to make both balances have identical future values of $98,690.

I set up an excel file that will let you alter the inputs and calculate a new offering price.

---------------------------------------------------------------

Set up the excel file exactly as follows: {Column A 3) = cell A3, etc.}

Column A:
1) Inputs:
2) Current balance on mortgage
3) Current interest rate on mortgage
(blank)
5) Proposed offering interest rate
(blank)
7) Rent equivalent
8) Fixed Down Payment
9) %-age Down Payment
(blanks)
16) Proposed Selling Price

Column B:
(blank)
2) 106421
3) 0.0635
(blank)
5) 0.048063
(blank)
7) =725-180
8) 6500
9) 0.059
(blanks)
16)Fixed Down
17) =-F10+B8

Column C:
16) %-age down
17) =-F10/(1-B9)

Column F:
1) 36
2) =B3/12
3) =-B2
4) 758.65
5) =FV(F2,F1,F4,F3)
(blanks)
8) =F1
9) =B5/12
10) =PV(F9,F8,F11,F12)
11) =B7
12) =F5

---------------------------------------------
Put in those exactly as they are written into exactly those cells (you can copy and paste for all I care ), add a little bit of formatting, and you've got yourself a calculator! Cells B3, B5 and B9 should all be percentages. Cells B2, B7, B8, B17, C17, F3-5, and F10-12 should all be dollar amounts.

You can then adjust the interest rates, and dollar figures as needed to evaluate some scnearios. Line 17 will tell you what the sales price should be before down payment.

Hope this helps!

[Bob B.]

How good are you with excel?

Given the figures you quoted, I'm getting an offering price of $110,200 to make both balances have identical future values of $98,690.

I set up an excel file that will let you alter the inputs and calculate a new offering price.

---------------------------------------------------------------

Set up the excel file exactly as follows: {Column A 3) = cell A3, etc.}

Column A:
1) Inputs:
2) Current balance on mortgage
3) Current interest rate on mortgage
(blank)
5) Proposed offering interest rate
(blank)
7) Rent equivalent
8) Fixed Down Payment
9) %-age Down Payment
(blanks)
16) Proposed Selling Price

Column B:
(blank)
2) 106421
3) 0.0635
(blank)
5) 0.048063
(blank)
7) =725-180
8) 6500
9) 0.059
(blanks)
16)Fixed Down
17) =-F10+B8

Column C:
16) %-age down
17) =-F10/(1-B9)

Column F:
1) 36
2) =B3/12
3) =-B2
4) 758.65
5) =FV(F2,F1,F4,F3)
(blanks)
8) =F1
9) =B5/12
10) =PV(F9,F8,F11,F12)
11) =B7
12) =F5

---------------------------------------------
Put in those exactly as they are written into exactly those cells (you can copy and paste for all I care ), add a little bit of formatting, and you've got yourself a calculator! Cells B3, B5 and B9 should all be percentages. Cells B2, B7, B8, B17, C17, F3-5, and F10-12 should all be dollar amounts.

You can then adjust the interest rates, and dollar figures as needed to evaluate some scnearios. Line 17 will tell you what the sales price should be before down payment.

Hope this helps!

Absolutely perfect! Thanks, that helps a lot.