Author Topic: Spreadsheet for future earnings given reinvestment and adding monthly  (Read 11677 times)

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
I new here but I have done quite a bit of reading across various blogs, listening to podcast, etc, etc. 

I understand from this article, "Do You Understand How Interest is Calculated on P2P Loans?", (http://www.lendacademy.com/do-you-understand-how-interest-is-calculated-on-p2p-loans/)
 on how the interest calculation is not like a savings account.
Quote
The monthly payment for your $100 loan at 10% is $3.23. Given a standard amortization schedule the first payment is split into $2.39 of principal and $0.83 of interest. So, your $100 investment has been paid down by $2.39 and the principal balance is now $97.61. S0, next month you will not receive interest on $100, you will receive interest on $97.61. Herein lies the big difference with amortized loans.


I'm considering making P2P lending a small part of my retirement investing (< 5%). I was searching for a spreadsheet that would allow me to do some simple analysis of what my potential earnings would be over say a
* 10 year period,
* investing more each month,
* reinvesting earnings ("compound interest"),
* assuming earning "9%"

Corresponding example of a savings account:
http://www.free-online-calculator-use.com/savings-account-interest-calculator.html#calculator


Certainly this topic has come up before but I have not had any luck finding a spreadsheet.
« Last Edit: April 06, 2014, 05:55:02 PM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #1 on: April 06, 2014, 09:03:25 PM »
Microsoft has many "retirement planning" Excel templates that you can download for free.

http://office.microsoft.com/en-us/templates/retirement-calculator-TC010370179.aspx

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #2 on: April 06, 2014, 10:24:14 PM »
Microsoft has many "retirement planning" Excel templates that you can download for free.
http://office.microsoft.com/en-us/templates/retirement-calculator-TC010370179.aspx
Thanks for the follow up. I checked that out.

I wanted to do future analysis on my LC assets using this criteria.
* 10 year period,
* investing more each month,
* reinvesting earnings ("compound interest"),
* assuming earning "9%"

It doesn't have to be spreadsheet but I wasn't sure any other tool allowed for the above.
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #3 on: April 06, 2014, 10:59:11 PM »
The tool you mentioned in your post could do this calculation, assuming 9% takes into account the loss rate.

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #4 on: April 07, 2014, 09:53:10 AM »
The tool you mentioned in your post could do this calculation, assuming 9% takes into account the loss rate.
Sorry, but I am confused by your answer. 

With savings you continue to get the same interest "income" and actually goes up due to compounding. 
With loan amortization, you continue to get less interest "income" after each month.

Unless I'm misunderstanding (see article in my first post), then these two are vastly different and the savings tool does not work like a loan/amortization tool.

Lost Rate defn: http://interestradar.wordpress.com/2012/08/30/definition-loss-rate/
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #5 on: April 07, 2014, 10:19:40 AM »
With savings you continue to get the same interest "income" and actually goes up due to compounding. 
With loan amortization, you continue to get less interest "income" after each month.

If you reinvest both principal & interest in new loans, you will get the compounding benefits.

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #6 on: April 07, 2014, 02:15:19 PM »
With savings you continue to get the same interest "income" and actually goes up due to compounding. 
With loan amortization, you continue to get less interest "income" after each month.
If you reinvest both principal & interest in new loans, you will get the compounding benefits.
Indeed. That is emphasised in the first comment to the original link I had.  Still I don't think you can use basic saving calculators to estimate amortization calculations, unless I'm still missing something.

http://www.lendacademy.com/do-you-understand-how-interest-is-calculated-on-p2p-loans/
Quote
Roy S September 12, 2012 at 6:20 pm
This is another reason to keep reinvesting your payments. If you reinvested the entire payment, then you will have $100.83 earning a return rather than just $97.61. Under this scenario, you run into one of the greatest inventions ever–compound interest! Of course, this follows your assumption of no service fees, that you can actually invest $3.23 (as opposed to the minimum $25 increments), and no lags in time from finding a Note that matches your criteria, waiting for the Note to be fully funded, and then waiting for the Note to originate. But all those caveats aside…compound interest!
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #7 on: April 07, 2014, 04:58:54 PM »
With savings you continue to get the same interest "income" and actually goes up due to compounding. 
With loan amortization, you continue to get less interest "income" after each month.
If you reinvest both principal & interest in new loans, you will get the compounding benefits.
Indeed. That is emphasised in the first comment to the original link I had.  Still I don't think you can use basic saving calculators to estimate amortization calculations, unless I'm still missing something.

Pretty sure you can.  You get less interest income $ amount, not less interest income % amount. 

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #8 on: April 07, 2014, 05:06:05 PM »
With savings you continue to get the same interest "income" and actually goes up due to compounding. 
With loan amortization, you continue to get less interest "income" after each month.
If you reinvest both principal & interest in new loans, you will get the compounding benefits.
Indeed. That is emphasised in the first comment to the original link I had.  Still I don't think you can use basic saving calculators to estimate amortization calculations, unless I'm still missing something.

Pretty sure you can.  You get less interest income $ amount, not less interest income % amount.
Can you show me examples of both because it doesn't make sense?
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

BruiserB

  • Sr. Member
  • ****
  • Posts: 417
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #9 on: April 07, 2014, 05:43:24 PM »
The quote you included above from Roy S is exactly how it works.  If you reinvest your payments each month, those payments become principal of new loans.  So in your example you actually have $100.83 earning interest for you for the second month, not $97.61.  And the next month this will grow again.

With LC you can't reinvest $3.23, but if you have lots of loans and you reinvest each time you have $25 in your account, your overall balance will pretty much behave like a savings account with monthly compounding....but you can't just use your average loan interest rate for the rate as you will have losses from defaults and service charges along the way, so you will need to assume a lower hypothetical interest rate...but the tool you used will be valid with that assumed rate.

Lovinglifestyle

  • Hero Member
  • *****
  • Posts: 897
    • View Profile
    • Email
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #10 on: April 07, 2014, 08:11:37 PM »
What about all the 3 week periods during the year for each $25 that isn't earning while it waits?

BruiserB

  • Sr. Member
  • ****
  • Posts: 417
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #11 on: April 07, 2014, 09:54:09 PM »
What about all the 3 week periods during the year for each $25 that isn't earning while it waits?

Assuming $25 is a great deal smaller than your total account balance, then the effect is pretty small.  It comes down to rounding error.  You could use the XIRR Excel function to measure your account's actual effective rate of return after fees, defaults, uninivested money, etc. and then use that effective rate to model your potential future returns.  With that function you just create a spreadsheet with all of your deposits and withdrawals from your LC account and your balance as of today.  It will then calculate the interest rate that would have created your current account balance given your deposit/withdrawal history. This rate won't be very realistic on a new account that hasn't experienced defaults, etc., but with time you will reach a steady state estimate of your ongoing return.

Here's a post Peter did detailing this method.

http://www.lendacademy.com/how-to-calculate-your-real-p2p-lending-return-with-xirr/
« Last Edit: April 07, 2014, 09:56:39 PM by BruiserB »

Thatguybil

  • Newbie
  • *
  • Posts: 39
    • View Profile
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #12 on: April 21, 2014, 08:57:50 PM »
Lascott

The regular investment tool will be an approximation.
It will make assumptions that are not true with lending club notes.

1: immediate reinvestment of all cash. This does not happen
2: immediate full funding of a loan once you invest
3: immediate issuance of a note once it funds
None of these are true.  The larger the portfolio, the longer the time frame the less the above will mater.

lascott

  • Hero Member
  • *****
  • Posts: 1409
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Spreadsheet for future earnings given reinvestment and adding monthly
« Reply #13 on: April 21, 2014, 09:57:14 PM »
Lascott, The regular investment tool will be an approximation. It will make assumptions that are not true with lending club notes.
1: immediate reinvestment of all cash. This does not happen
2: immediate full funding of a loan once you invest
3: immediate issuance of a note once it funds
None of these are true.  The larger the portfolio, the longer the time frame the less the above will matter.
Indeed the more I'm investing the more I realized you can't keep track of various $25 notes.  People cancel applications on their own, LC cancels the loan, etc.  To your point, you get mails like "Order Complete: One Note not issued".

I am using automation to invest/reinvest my cash (new or from loans that were fund, etc) as quickly as possible.  Fully understand your point about lag tho. You can accrue interest/earnings unless the notes are funded and issued.

Statuses where your money is not working for you!! https://www.lendingclub.com/public/loansHelp.action
Quote
The status of each loan is also shown:
In Review: Loan requests that are pending a final review by our Credit Department to ensure the applications are in good standing before they are issued.
Expired: Loan requests that did not receive full funding and were not issued.
Removed: Loans that were removed from active listings based on credit decisions, inability to verify identity of the borrower, or based on content policies
In Funding: Loan requests that are listed on the site and receiving funding.
Partially Funded: Loans that did not receive full funding and are pending partial funding acceptance by the borrower
« Last Edit: April 21, 2014, 10:47:47 PM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y