trying to calculate overall interest for a given period

michaelc
Member of DD Central

Say No To T.D.S.

Posts: 5,707

Likes: 2,983

trying to calculate overall interest for a given period Jul 30, 2017 18:35:30 GMT

Quote

Post by michaelc on Jul 30, 2017 18:35:30 GMT

I wonder if someone might be kind enough to help me? I'm (clearly) not an accountant but I'm trying to model my "books" at funding circle from the transaction statements. I not interested (or not close to being capable of) modelling each loan but just want an overall picture.

What have so far is three books or accounts:
- funding circle main account
- loan account
- income/interest account

So I go through every transaction and if its an interest repayment, servicing fee or similar it goes to the income account. If its a purchase of a loan, sale of loan or capatial repayment is goes to the loan account.

My theory is I can then perform calculations like interest based on how much I've invested (the loan account) and how much interest I receive (income account).

What I can't get my head around, is how to treat defaults? I think they only show up in the transactions when there is a recovery? And in general are these accounts enough to know how much I have invested and how much I've earned/lost over time? I was thinking of e.g. calculating an average balance in the loan account for a given month and comparing that with the amount the income account increased over that month (expressing one of the other to give a crude interest rate).

I just get the feeling I'm doing something half right but missing something in the model.

Any thoughts or advice would be most gratefully received.

william0101

Posts: 65

Likes: 23

trying to calculate overall interest for a given period Jul 30, 2017 21:59:37 GMT

Quote

Post by william0101 on Jul 30, 2017 21:59:37 GMT

There isn't enough detail in the transaction statements alone to do this.

Take a look at the recent "spreadsheet hell" thread to see if that gets you any further.

I'm generating full double entry transactions but it does need some extras

I also haven't had a "default never getting it back" yet but the accounting isn't complicated

Last Edit: Jul 30, 2017 22:05:55 GMT by william0101

voss
Member of DD Central

Posts: 153

Likes: 84

trying to calculate overall interest for a given period Jul 31, 2017 19:08:56 GMT

Quote

Post by voss on Jul 31, 2017 19:08:56 GMT

I gave up trying to do this and now I just take:

Funding Circle total (from summary page) at start of period
ditto at end of period
Any amounts paid in or withdrawn with dates

and then put those few figures through XIRR. Result: bliss (and lots of freed up time)

Of course, Fumbling Calculations makes mistakes but in my experience these are almost always sorted out by them fairly quickly without any intervention from me. So I've learnt to stop worrying about it.