I'm having a problem with a Yield to Maturity formula and hoping that someone can provide some guidance. I should probably state that I'm trying to use it to calculate the yield on a loan as opposed to a bond. But like a bond, the loan would be purchased at a discount and then repaid at maturity at it's current face value.
For purposes of simplicity, let's say the loan's current balance is $1 million and I can buy it for $950,000. I'll also assume that the current coupon or interest rate is 7% and that maturity is in 4 years. Any bond calculator that I locate on the web, shows me that the Yield to Maturity is 8.53%.
However, when I plug this info into Excel's YIELDMAT formula I get absurd values depending on whatever Issue Date I insert. Therein lies the problem; Excel appears to place too much weight on the Issue Date, whereas other web-based calculators look at the yield from today to maturity.
A previous worksheet used a GetReturn formula that calculated the return based on the current balance, the discounted purchase price (as a percentage of the current balance), the monthly interest payment, the current coupon rate, and the remaining term to maturity (in months) - in that order. Using the assumptions above, the fomula would be:
=GetReturn(A1,A2,A3,A4,A5)
where A1 would equal $1,000,000; A2 would equal 95%, A3 would equal $70,000, A4 would equal 7% and A5 would equal 48.
This GetReturn formula calculates the yield fairly closely to the yield achieved through the web-based bond calculators. I've tried copying that formula to my new worksheet, but unfortunately it will not work and I can not locate any help information on this formula nor do I recognize it as an Excel-based formula. Any thoughts/ideas out there?