Skip to main content

Author Topic: calculate the right YTM with R

g
  • Posts: 3
calculate the right YTM with R
OP: November 22, 2016, 12:00:00 AM
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%

  • IP logged

  • *****
  • Hero Member
  • Posts: 612
calculate the right YTM with R
#1: November 22, 2016, 12:00:00 AM
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.
  • IP logged

T
  • *****
  • Hero Member
  • Posts: 6586

T
  • *****
  • Hero Member
  • Posts: 6586
calculate the right YTM with R
#3: November 23, 2016, 12:00:00 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.
  • IP logged

T
  • *****
  • Hero Member
  • Posts: 6586

R
  • ****
  • Sr. Member
  • Posts: 483
calculate the right YTM with R
#5: November 30, 2016, 12:00:00 AM
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!
  • IP logged

T
  • *****
  • Hero Member
  • Posts: 6586

T
  • *****
  • Hero Member
  • Posts: 6586

b
  • Posts: 1
calculate the right YTM with R
#8: December 01, 2016, 12:00:00 AM
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
  • IP logged

T
  • *****
  • Hero Member
  • Posts: 6586

T
  • *****
  • Hero Member
  • Posts: 6586

T
  • *****
  • Hero Member
  • Posts: 6586

T
  • *****
  • Hero Member
  • Posts: 6586
calculate the right YTM with R
#12: December 01, 2016, 12:00:00 AM
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 ...
  • IP logged

f
  • Posts: 132

T
  • *****
  • Hero Member
  • Posts: 6586