Need Formula help for Discounting a Partially Amortized Loan

[OurRealtor]

I am as seller, unable to sell my waterfront cottage, will consider an STB for $180,000 at 6% amortized over 25 years. I feel this strategy will make my property more saleable and attract potential buyers. As you know financing through traditional sources is extremely difficult given the property`s distant location, unique design, age, lack of services and limited market appeal. I plan to sell the mortgage prior to closing, but needs a cost estimate concerning the discount.



A local mortgage broker advises me that a private investor knowledgeable in that particular cottage area would seriously consider the mortgage, but requires an 11% yield.



To establish what the potential lender will pay (present value based on 11% yield), the mortgage must be discounted at 11%. In other words, the face amount must be reduced in order to increase the overall return from 6% to 11%.



Assume that the my mortgage is based on a 5-year term, with the investor`s yield expectation remaining the same.



The cost of sale would be reduced significantly given a balloon payment at end-of-year five (EOY 5). The outstanding balance at the end of year five is $161,706.07. Alter the amortization of the mortgage to 60 payments (five years)



Please provide a maths formula for the present value calculated is $147,933.18 with my cost now at $32,066.82 (180,000`147,933.18).



If I had limited the term to two years, the present value would rise further to $164,679.43 with Seller cost at $15,320.57 (180,000`164,679.43).



I don't know how my HP is calculating PV as present value =$147,933.18, any idea , please guide me via formulas and hints. I like to verify it by formulas prior to any decision.



Thanks

 

[bizaro86]

Hello,



You've asked a complicated question, which might be why nobody has answered it until now.



You can calculate the present value by making loan calculations assuming a loan made from someone else at 11% to your buyer, with payments as they will be, and a final value as it will be. (I used a mortgage calculator and figured the monthly payments are ~1151.66)



You'll have to iterate (perform the calculation more than once) to get the values you want, by comparing your input to your output.



The formula you want is:



Balance = Loan Amount*(1+i)^n - (P/i)*((1+i)^n - 1)

where i = interest rate per payment period (ie 0.11/12 months)

n= number of payments (60)

P=payment amount (~1151)

and Loan Amount is the present value of the money



Iterate this with differing loan amounts until you get the balance to come out the same as the balloon payment at the end of the loan.



Hope that helps,



Michael

 

[OurRealtor]

I just read your reply today, but I have figured it out already 2 weeks ago. The only difference is that you have to reminded me about using negative sign on both parts on right side of equation. Tell me why both are negative signs on right side of equation?



The formula used to compute loan payments is very similar, but as is appropriate for


a loan, it assumes that all monthly payments "p" are made at the

end of each period:

F = -P(1+i)^n - [p((1+i)^n - 1)/i]


Note that this formula can also be used for investments if you need to assume that


they are made at the end of each period. With respect to loans, the formula isn't


very useful in this form, but by setting "F" to zero, the future value (one hopes) of


the loan, it can be manipulated to yield some more useful information.


Now F=161,706.0669, i=0.008963394 (1/12th of 10.75607272%), n=60 (5 Yrs), p=Monthly


Payments=1151.651915


161,706.0669=-1.708144466P-1151.651915X79.00405427


So, Present Value= $147,933.177

 

[bizaro86]

[quote user=OurRealtor]The only difference is that you have to reminded me about using negative sign on both parts on right side of equation. Tell me why both are negative signs on right side of equation?





I'm pretty sure only one of the terms on the right side should have a negative sign, since if you think about what they mean, they should be opposites.



The first term on the right side is the initial principal plus compound interest. The value of the loan gets bigger over time (more positive) if no payments were made. (set p=0)



The second term on is the repayments by the borrower. It should have an opposite sign to the first term, since it causes the final value of the loan to decrease, offsetting the interest charged.



Regards,



Michael

 

[OurRealtor]

Thanks for clarification. But you havn't said or agreed that last example doesn't required iteration.


I am stucked up in iteration of another example. Its not converging explain , how can I make it converge ?



Buyer Kilpatrick is negotiating a conventional first mortgage for $220,000 on a rural property.

A private investor, through a mortgage broker, has offered a 15-year amortized, 15-year term

mortgage at 7.0%. Kilpatrick must incur certain costs (over and above typical legal and survey

costs) which will be deducted at closing including:

Bonus @ 150 basis points 3,300.00

Administration Fee 500.00

Kilpatrick wants to know his true rate of interest given these deductions.



====



True Interest Rate`











Partially Amortized Mortgage





If Kilpatrick was arranging a partially amortized mortgage with a 5-year term, an additional


step is required involving the balance owing at







EOY 5.



Calculate the Effective Interest Rate

The net proceeds received by Kilpatrick are $220,000`$3,300`$500 = $216,200. Adjust the

present value and amortization of the mortgage and recalculate the interest rate:

216200 PV

60 N I/YR

The effective interest rate is 7.36%. The nominal or stated rate would be higher, but is not

calculated for this exercise. (For additional discussion of nominal vs. effective rate calculations,



When I use formula , its not converging, I don't know how this calculator is using convergent formula to arrive at aboove interest rate.

 

[bizaro86]

[quote user=OurRealtor]But you havn't said or agreed that last example doesn't required iteration



I haven't had time to go through and verify it, I have a lot going on this week.



As to your next example not converging, have you tried inputting a better initial guess? That is one way of "forcing" something to converge, is to input a very good estimate initially. Maybe even just put in the answer, and see if both sides match.



Regards,



Michael

 

[OurRealtor]

[quote user=bizaro86][quote user=OurRealtor]But you havn't said or agreed that last example doesn't required iteration



I haven't had time to go through and verify it, I have a lot going on this week.



As to your next example not converging, have you tried inputting a better initial guess? That is one way of "forcing" something to converge, is to input a very good estimate initially. Maybe even just put in the answer, and see if both sides match.



Regards,



Michael




I am using this equation as monthly payment will be much greater ($4345.896812) to pay off 220K in just 5 yrs(60 months)

4345.9=i/[1-(1+i)`-60]x220,000



Answer is 7.36, so i=7.36/12~0.00613 monthly rate, which approximately equals both the sides but suppose I was not aware of answer, iteration will continue throwing increasing rate and how we know its the time to stop iterations?

 

[bizaro86]

If your answer is getting further and further away from the correct value, you know it's unstable with your initial guess. Some formula are unstable, and will not converge when iterated.

 

[OurRealtor]

[quote user=bizaro86] If your answer is getting further and further away from the correct value, you know it's unstable with your initial guess. Some formula are unstable, and will not converge when iterated.


post me with detail iteration, or other alternative, I still need your review of above formula approach I used to check iterations. Is there other alternative method?