P2P Lending Best Practices: Tracking Interest Earned

Whether we use our IRA or not to invest in P2P loans, we still have to track if and how we are making money. Like all loans, we make money by earning interest. That interest is taxable. How do we track interest for ourselves? Do we just take the platform's word for it that they are giving us the correct interest amounts?

The Tool for the Job: The Amortization Table

The way we calculate interest on any of our loans is through the use of an amortization table. You may have seen one of these when you got a mortgage if you wanted to see a table of how much interest would be deductible on your federal income taxes each year. So how do you get one?

Microsoft Excel users can download this template and have it on hand to calculate and then save if you want to know what the interest on one of your loan listings looks like.

Open Office users can use this one

Apple iwork can use this one

Here is one of the amortization tables that I use, for a typical loan I might invest in with a lender rate (listed rate less the 1% service fee) of 11% for $100, which is one of the loan increments that I use. Let's see what it looks like. Note that the principal plus interest always add up to the total payment, which in this case is $2.17 per month for 60 months and that the principal balance declines each month by the principal portion of the payment. In month one, the principal portion is $1.25 so the principal balance is now not $100, but $98.75 leading into month two.

Loan Information       [42] Summary    
Loan Amount 100.00 Rate (per period) 0.917%
Annual Interest Rate 11.00% Number of Payments 60
Term of Loan in Years 5 Total Payments 130.55
First Payment Date 11/1/2013 Total Interest 30.55
Payment Frequency Monthly Est. Interest Savings (0.10)
Compound Period Monthly .
Payment Type End of Period .
Monthly Payment 2.17  
[42]
Amortization Schedule             TRUE
No. Due Date Payment Additional Payment Interest Principal Balance
              100.00
1 11/1/13 2.17     0.92 1.25 98.75
2 12/1/13 2.17     0.91 1.26 97.49
3 1/1/14 2.17     0.89 1.28 96.21
4 2/1/14 2.17     0.88 1.29 94.92
5 3/1/14 2.17     0.87 1.30 93.62
6 4/1/14 2.17     0.86 1.31 92.31
7 5/1/14 2.17     0.85 1.32 90.99
8 6/1/14 2.17     0.83 1.34 89.65
9 7/1/14 2.17     0.82 1.35 88.30
10 8/1/14 2.17     0.81 1.36 86.94
11 9/1/14 2.17     0.80 1.37 85.57
12 10/1/14 2.17     0.78 1.39 84.18
13 11/1/14 2.17     0.77 1.40 82.78
14 12/1/14 2.17     0.76 1.41 81.37
15 1/1/15 2.17     0.75 1.42 79.95
16 2/1/15 2.17     0.73 1.44 78.51
17 3/1/15 2.17     0.72 1.45 77.06
18 4/1/15 2.17     0.71 1.46 75.60
19 5/1/15 2.17     0.69 1.48 74.12
20 6/1/15 2.17     0.68 1.49 72.63
21 7/1/15 2.17     0.67 1.50 71.13
22 8/1/15 2.17     0.65 1.52 69.61
23 9/1/15 2.17     0.64 1.53 68.08
24 10/1/15 2.17     0.62 1.55 66.53
25 11/1/15 2.17     0.61 1.56 64.97
26 12/1/15 2.17     0.60 1.57 63.40
27 1/1/16 2.17     0.58 1.59 61.81
28 2/1/16 2.17     0.57 1.60 60.21
29 3/1/16 2.17     0.55 1.62 58.59
30 4/1/16 2.17     0.54 1.63 56.96
31 5/1/16 2.17     0.52 1.65 55.31
32 6/1/16 2.17     0.51 1.66 53.65
33 7/1/16 2.17     0.49 1.68 51.97
34 8/1/16 2.17     0.48 1.69 50.28
35 9/1/16 2.17     0.46 1.71 48.57
36 10/1/16 2.17     0.45 1.72 46.85
37 11/1/16 2.17     0.43 1.74 45.11
38 12/1/16 2.17     0.41 1.76 43.35
39 1/1/17 2.17     0.40 1.77 41.58
40 2/1/17 2.17     0.38 1.79 39.79
41 3/1/17 2.17     0.36 1.81 37.98
42 4/1/17 2.17     0.35 1.82 36.16
43 5/1/17 2.17     0.33 1.84 34.32
44 6/1/17 2.17     0.31 1.86 32.46
45 7/1/17 2.17     0.30 1.87 30.59
46 8/1/17 2.17     0.28 1.89 28.70
47 9/1/17 2.17     0.26 1.91 26.79
48 10/1/17 2.17     0.25 1.92 24.87
49 11/1/17 2.17     0.23 1.94 22.93
50 12/1/17 2.17     0.21 1.96 20.97
51 1/1/18 2.17     0.19 1.98 18.99
52 2/1/18 2.17     0.17 2.00 16.99
53 3/1/18 2.17     0.16 2.01 14.98
54 4/1/18 2.17     0.14 2.03 12.95
55 5/1/18 2.17     0.12 2.05 10.90
56 6/1/18 2.17     0.10 2.07 8.83
57 7/1/18 2.17     0.08 2.09 6.74
58 8/1/18 2.17     0.06 2.11 4.63
59 9/1/18 2.17     0.04 2.13 2.50
60 10/1/18 2.52     0.02 2.50 0.00

 

What The Amortization Table Tells Us

What you see above should look familiar if you are a subscriber (you can subscribe here) because this is very close to what I use when I evaluate my portfolio performance each month. I use  the interest on each loan, principal paid down and principal balance remaining in order to calculate my ROI (Interest Earned/Principal balance remaining). Even though our amount going to interest declines each month down to a paltry 2 cents in month 60, the principal balance keeps declining as well as the principal balance remaining in month 59 is only $2.50 out of our original $100.

Tip #1: If you ever want to sell your notes on the secondary market, you should run an amortization table first and see what your true interest earned and principal balance remaining is so you can determine what price you think is fair to sell your note.

Tip #2: See Tip #1 and follow it if you ever want to use the secondary market to BUY a note. You need to know what the true principal balance remaining is if you want to 'buy right' and get the note at a price where you can make the interest rate that you want.

Tip #3: If you have a loan default, then you should run an amortization table so you have an accurate indication of loan losses that you can declare on your taxes, if you can declare the loss based on your own tax situation. Save and print a copy in case you need to provide one to your CPA or the IRS.

Lastly, you probably see a big column in the middle for additional payments. This is to account for the fact that in a given month our borrower may choose to pay more than the $2.17 we are expected and owed. If for instance, he pays us $4, then that additional $1.83 is used to pay down the principal balance remaining and then we would have to account for that in our table, as well as in our calculation of ROI in that month and going forward since the principal balance would be lower than is stated here in the table for each future month after the additional payment.

About the author

Stu Stu Lustman, the author of this post, is a Credit Analyst by trade trying to bring Commercial Credit Analysis techniques to the world of Peer to Peer Lending. Check me out on Twitter, LinkedIn and Google+

1 thought on “P2P Lending Best Practices: Tracking Interest Earned

Leave a Comment