Author Topic: calculate the right YTM with R  (Read 4974 times)

gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
calculate the right YTM with R
« on: November 22, 2016, 06:49:02 PM »
Some posts are complaining about the YTM provided by foliofn. I found that the YTM is usually wrong when a note is less than 1 year or has a discount rate. If your strategy is to find a decent YTM with bigger discount rate and less remaining payments, their YTM is meaningless and you have to calculate by yourself. I would like to share my algorithm. Any feedback and comments are welcome.

The basic idea is to resolve r, monthly interest rate, from this formula:

a=p*(r*(1+r)^n/((1+r)^n-1))

Where a is monthly payment, p is askPrice (assume that you buy it with askPrice) and n is the remaining payments. p and n are provided in the CSV file.
a is not provided so we have to calculate it. We still use the same formula, but for this time p is outstanding principal and r is the original monthly interest rate . Monthly interest rate is not directly provided, but you get it by dividing annual interest rate by 12. Some people would argue that r is a geometrical mean instead of a arithmetic mean. Well, I had the same point until I found that it is more common to use arithmetic mean. If we want to take into account the monthly 1% service fee, adjust a by multiplying it with 0.99.  Once we get a, we can start resolving the equation with a single unknown variable r. It is a nonlinear equation and there is no analytical solution for r. So we have to use numerical method to get an approximation of r.  There are many ways to do numerical calculation. The easiest way is probably Newton method.   

finally YTM=12*r*100%

Here is the R code to calculate Yield. You have to add the column of MonthlyPayment to the original dataframe before you pass it to this function.

getYield <- function(a,tol=1E-12,x0=1,N=20) {
  m<-as.numeric(a["MonthlyPayment"])
  n<-as.numeric(a["Remaining.Payments"])
  pr<-as.numeric(a["AskPrice"])
 
  h <- 0.001
  i <- 1; x1 <- x0
  p <- numeric(N)
  while (i<=N) {
    df.dx <- (f(x0+h,m,pr,n)-f(x0,m,pr,n))/h
    x1 <- (x0 - (f(x0,m,pr,n)/df.dx))
    p <- x1
    i <- i + 1
    if (abs(x1-x0) < tol) break
    x0 <- x1
  }

  return(p[i-1]*12*100)
}

f<-function(r,a,p, n)
{
  y<-a-p*(r*(1+r)^n/((1+r)^n-1))
  return(y)
}

A real example
https://www.lendingclub.com/foliofn/browseNotesLoanPerf.action?showfoliofn=true&loan_id=31317108&order_id=130405930&note_id=60503781

Asking Price   $9.34
Interest rate 15.61%
Outstanding Principal   $9.66
Remaining Payments 12

r=0.1561/12= 0.01300833

a=p*(r*(1+r)^n/((1+r)^n-1))=9.66*(0.01300833*(1+0.01300833)^12/((1+0.01300833)^12-1))=0.8746782, which matches the monthly payment provided by Foliofn

finally, let's resolve the equation with Newton's Method

0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))

r= 0.018425

YTM=r*12*100%=22.1%

« Last Edit: November 30, 2016, 12:50:53 AM by gaoc »

Fred93

  • Hero Member
  • *****
  • Posts: 2129
    • View Profile
Re: calculate the right YTM with R
« Reply #1 on: November 22, 2016, 07:07:40 PM »
One problem with your formula...  It presumes equally spaced payments.  When you buy a loan via folio, the time to first payment that you receive is not the same as the time between payments.  Therefore, a slightly more complicated formula is required.

gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: calculate the right YTM with R
« Reply #2 on: November 22, 2016, 07:32:42 PM »
One problem with your formula...  It presumes equally spaced payments.  When you buy a loan via folio, the time to first payment that you receive is not the same as the time between payments.  Therefore, a slightly more complicated formula is required.
Thank you for the comments. You are right. If you count on days, the Yield is slightly different but it can only be higher or equal (assuming no late payment).  Time between now and the next payment must be less than the regular time interval. In other words you get a full month payment in less than or equal to one month time, so the real return can only be higher or equal.   
« Last Edit: November 22, 2016, 07:34:20 PM by gaoc »

Fred93

  • Hero Member
  • *****
  • Posts: 2129
    • View Profile
Re: calculate the right YTM with R
« Reply #3 on: November 23, 2016, 01:36:55 AM »
Another problem is that your formula doesn't take into account the 1% of each payment that LC takes as its fee.  The effect this has on YTM depends on the remaining # payments in the loan.  For loans with few payments remaining, the effect on YTM is large.

gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: calculate the right YTM with R
« Reply #4 on: November 30, 2016, 01:35:45 PM »
Another problem is that your formula doesn't take into account the 1% of each payment that LC takes as its fee.  The effect this has on YTM depends on the remaining # payments in the loan.  For loans with few payments remaining, the effect on YTM is large.

That is true. 1% change of monthly payment could result in 2%-3% change of YTM in the example above. To take into account 1% fee, change the monthly payment to 0.99*a

Rob L

  • Hero Member
  • *****
  • Posts: 2028
    • View Profile
Re: calculate the right YTM with R
« Reply #5 on: November 30, 2016, 03:02:48 PM »
Another complication is when the borrower has made one or more early payments. For example:
https://www.lendingclub.com/foliofn/loanPerf.action?loan_id=38528044&order_id=52420245&note_id=67624504


The final payment will not be the usual monthly payment amount but something less.
In this case the final payment is $2.1512 vice the usual $3.1646. See the following thread for discussion of the example:
http://forum.lendacademy.com/index.php/topic,3633.0.html

The correct calculation provided by RaymondG and results checked by Fred93 is:



Folio said the YTM was 7.72% but the correct YTM was 8.399%. Quite a significant difference!

gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: calculate the right YTM with R
« Reply #6 on: November 30, 2016, 06:40:40 PM »
Another complication is when the borrower has made one or more early payments. For example:
https://www.lendingclub.com/foliofn/loanPerf.action?loan_id=38528044&order_id=52420245&note_id=67624504


The final payment will not be the usual monthly payment amount but something less.
In this case the final payment is $2.1512 vice the usual $3.1646. See the following thread for discussion of the example:
http://forum.lendacademy.com/index.php/topic,3633.0.html

The correct calculation provided by RaymondG and results checked by Fred93 is:



Folio said the YTM was 7.72% but the correct YTM was 8.399%. Quite a significant difference!

That is true. Early payments or late payments after buying notes is not covered by this method. The method in this post is to do YTM projection rather than calculate YTM after mature. It is necessary to assume evenly monthly payment. It is another story when calculating real YTM after mature. You can always use the fundamental bond model to calculate yield after mature. The model is
price=c1/(1+r)+ c2/(1+r)^2+c3/(1+r)^3+...+cN/(1+r)^N
price is the price you paid for, cN is the nth payment, it can be any positive value or 0. So this will cover early and late payments. (LC note is a special case of the model when c1=c2=c3=....=cN)



 

Rob L

  • Hero Member
  • *****
  • Posts: 2028
    • View Profile
Re: calculate the right YTM with R
« Reply #7 on: December 01, 2016, 11:01:30 AM »
That is true. Early payments or late payments after buying notes is not covered by this method.

Yes, I understand. The example provided does not include any early or late payments or anything else after the Folio sale. It is a YTM projection, not a post maturity computation. YTM is calculated based on the initial loan terms as issued (they don't change because of a Folio trade), the Folio listing (remaining principal and seller's offer price), the date of the Folio purchase, and the 1% service fee.

The complication arises when the borrower has made early or partial payment(s) prior to listing the loan on Folio (and that is not unusual). The monthly payment amount is fixed at the time the loan is issued. Allocation of each fixed monthly payment to principal repayment amount and interest amount is always based on the remaining principal balance of the prior month and the interest rate of the loan. Any early or partial payments prior to the Folio listing "decouples" the remaining principal from the fixed monthly payments so that there can be no expectation that a final payment of the original fixed payment amount will exactly pay off the loan (i.e. c1 = c2 = c3 ... not equal cN). The number of remaining payments at the time of the Folio sale is, in a way, not an integer; its a real number where the fractional part is the final payment as a percent of a normal payment. The exact amount of the final payment may be exactly calculated for any Folio listing and used to compute projected YTM.

FWIW, I've read tons of posts regarding Folio's YTM calculation and no one has been able to successfully duplicate it as far as I know. It's a mystery. I tried and failed.
In my own calculation I simplify by computing IRR rather than XIRR, but do account for the fact that the final payment may not necessarily be the same as all the usual monthly payments. The example spreadsheet I've shown in the previous post is more accurate than the code I used back when I was selling some of my notes on Folio.

Oh yeah, one more thing. It's easy to see why the fewer payments remaining then the larger effect the final payment amount has on the YTM calculation. If the YTM calculation does not account for this then the fewer payments remaining the more inaccurate the YTM calculation will be.
« Last Edit: December 01, 2016, 11:11:51 AM by Rob L »

bradmc

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: calculate the right YTM with R
« Reply #8 on: December 01, 2016, 03:43:23 PM »
Not going to say I understand the math here, because I absolutely do not.  But I can replicate the first part and run it through an online calc, but not the second part.
Quote
a=p*(r*(1+r)^n/((1+r)^n-1))=9.66*(0.01300833*(1+0.01300833)^12/((1+0.01300833)^12-1))=0.8746782, which matches the monthly payment provided by Foliofn
The below part I ran through multiple online calcs and there is something in the formula they do not like.  Below is the online calc I used.
https://www.mathpapa.com/algebra-calculator.html
Is the formula wrong or am I entering something wrong on the online calculator?
Quote
finally, let's resolve the equation with Newton's Method

0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))

Thanks in advance.  I am trying to understand how to calculate basic YTM on FOLIO.  I understand that the YTM on FOLIO might be wrong and want to make sure that what is listed is at least close.  I have a hard time believing that what they list for notes under 12 months is even right.  Not concerned with prepayment or multiple payments that have occurred.  I do not buy notes that have had multiple payments and can not control early payment. 

gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: calculate the right YTM with R
« Reply #9 on: December 01, 2016, 05:34:31 PM »
The complication arises when the borrower has made early or partial payment(s) prior to listing the loan on Folio (and that is not unusual). The monthly payment amount is fixed at the time the loan is issued. Allocation of each fixed monthly payment to principal repayment amount and interest amount is always based on the remaining principal balance of the prior month and the interest rate of the loan. Any early or partial payments prior to the Folio listing "decouples" the remaining principal from the fixed monthly payments so that there can be no expectation that a final payment of the original fixed payment amount will exactly pay off the loan (i.e. c1 = c2 = c3 ... not equal cN).
yes, it is tricky to handle this case. LC doesn't recalculate the monthly payment after a early payment. It is not an issue for LC investors because your remaining money earn the monthly interest anyway. You still get the same YTM even the borrower pays off for the first payment. But it is a risk for foliofn investors especially when you buy a note with markup. I lose money once when I bought a note with markup and the borrower pay off immediately. 

To better estimate YTM let's change the model a little bit for the case where cN is not equal to the rest.

p=a*[(1+r)^(N-1)-1]/[r*(1+r)^(N-1)]+cN/(1+r)^N

a=c1=c2=c3=...=c(N-1), p is still the ask price

let's see how we resolve r from this new model. It is easy to calculate a, but we need a formula to calculate cN and N. When borrowers pay early, N may not be equal to the original terms. The excel in your comment is a good way to get cN and N.

Let's have a try with the example in the excel

3.1646*((1+r)^22-1)/(r*(1+r)^22)+2.1512/(1+r)^23=66

Solve the equation with Newton's Method
r = 0.0071976

YTM=r*12*100%=8.63712%






gaoc

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: calculate the right YTM with R
« Reply #10 on: December 01, 2016, 05:38:50 PM »
https://www.mathpapa.com/algebra-calculator.html

This website may not be able to resolve nonlinear equation. Try this one https://www.wolframalpha.com/. Just copy and past "0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))" to the input. It will automatically recognize the unknown variable and solve it.

bradmc

  • Newbie
  • *
  • Posts: 4
    • View Profile
Re: calculate the right YTM with R
« Reply #11 on: December 01, 2016, 06:48:04 PM »
https://www.mathpapa.com/algebra-calculator.html

This website may not be able to resolve nonlinear equation. Try this one https://www.wolframalpha.com/. Just copy and past "0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))" to the input. It will automatically recognize the unknown variable and solve it.

it came up with 0.00184273








Rob L

  • Hero Member
  • *****
  • Posts: 2028
    • View Profile
Re: calculate the right YTM with R
« Reply #12 on: December 01, 2016, 07:56:13 PM »
Took the data from the loan in your original post and stuffed it into an Excel spreadsheet like my previous example:



Under the category of how many angels can dance on the head of a pin, or you learn something new every day, I discovered the precise way that LC computes monthly payment. When I computed it using the Excel PMT() function I got $0.8741198194. When you mouse over the PMT on Folio you get a slightly different value. The precise value that LC uses comes from the total monthly payment of the original loan. The monthly payment for any note is (Note Initial Investment / Total Loan Amount) * Total Monthly Payment. This yields $0.8741322314 and  is exactly the value displayed when doing a mouse over. How bout that sports fans...

Even when incorporating that slight difference a final payment equal to the previous payments does not exactly square the books and take the remaining principal to zero. To square the books the final payment must be the remaining principal balance plus the monthly interest on that remaining principal balance. There's no way around it except for LC to say never mind, it's just rounding and I very much doubt they do that. In this example I think LC would require the borrower to pony up an additional $5.92 in the final payment but I really don't know how they handle this.

On a different note; why would anyone want to buy such a note anyway? By this point in the loan (12 months remaining) one is paying LC quite a lot just to get their investment back (1% of $9.66 to get your money back and 1% of $0.84 total interest you earn). Wouldn't take but a few bad apples to upset the cart. Maybe a good thing, I dunno ...

fliphusker

  • Sr. Member
  • ****
  • Posts: 463
    • View Profile
    • Email
Re: calculate the right YTM with R
« Reply #13 on: December 04, 2016, 02:31:11 AM »
I could not replicate this either.  Hence the trust I hand over to NSR.
https://www.mathpapa.com/algebra-calculator.html

This website may not be able to resolve nonlinear equation. Try this one https://www.wolframalpha.com/. Just copy and past "0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))" to the input. It will automatically recognize the unknown variable and solve it.

it came up with 0.00184273

Rob L

  • Hero Member
  • *****
  • Posts: 2028
    • View Profile
Re: calculate the right YTM with R
« Reply #14 on: December 04, 2016, 11:20:12 AM »
I could not replicate this either.  Hence the trust I hand over to NSR.
https://www.mathpapa.com/algebra-calculator.html

This website may not be able to resolve nonlinear equation. Try this one https://www.wolframalpha.com/. Just copy and past "0.8746782 = 9.34*(r*(1+r)^12/((1+r)^12-1))" to the input. It will automatically recognize the unknown variable and solve it.

it came up with 0.00184273

Actually it comes up with 0.0184273. Multiply by 12 to annualize this monthly interest rate and then by 100 to convert fraction to percent.
r = 0.0184273 * 12 * 100 = 22.11276%  That's the YTM.
It's very neat that Wolfram recognizes that r is the unknown and solves for it iteratively.