Poll

Do you have multiple portfolios in your account?

1 portfolio
3 (15.8%)
2 portfolio
0 (0%)
3+ portfolio
16 (84.2%)

Total Members Voted: 19

Author Topic: Analysing performance of multiple portfolios in a account  (Read 4011 times)

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Analysing performance of multiple portfolios in a account
« on: December 29, 2013, 12:08:33 AM »
Hi,

I have been using a worksheet to track performance of multiple portfolios in my LC account. I would like to share it to thank Peter Renton and his Lend Academy. It's a great place for P2P information, and best, it make me feel I am in a group.

About the worksheet. It's shared through docs.google.com. So, if you are interested, please browse the following link, and then click the big file snapshot icon, then click Download icon at the right-bottom corner.

https://drive.google.com/folderview?id=0B0PqZljirBQDbzcyT2hOOFhwaWs&usp=sharing

In tab "MyNote", a simple introduction of how-to-use is included. The most important calculation is about the return rate of each portfolio. A simplified method is used. It would be good enough if the issue-age range is set not too wide, such as less than 5 years. It did show clearly that one of my current models had much worse lose and make the real return of that portfolio is much lower than expectation. I stopped that model.

It includes loans sold in Foliofn with manual updating. With modification, it could easily include loan purchases in Foliofn too. I have not buy notes from Foliofn for a while, so not coded it.

Hope you like this tool. And, in advance,

Happy New Year!

Thanks,

Raymond G.
« Last Edit: December 29, 2013, 10:38:21 AM by RaymondG »

Zach

  • Administrator
  • Hero Member
  • *****
  • Posts: 622
    • View Profile
    • Email
Re: Analysing performance of multiple portfolios in a account
« Reply #1 on: December 29, 2013, 01:09:46 AM »
Hi Raymond-

Thanks for sharing your analysis spreadsheet. Could you explain how you're accessing step #2 (Sold notes)? Are you getting this info from FolioFN?

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Analysing performance of multiple portfolios in a account
« Reply #2 on: December 29, 2013, 01:29:52 AM »
Hi Raymond-

Thanks for sharing your analysis spreadsheet. Could you explain how you're accessing step #2 (Sold notes)? Are you getting this info from FolioFN?

If you do not have copies of all your notes data in past, it may be a problem to get all the information, especially which portfolio the notes belong to.

In my case, I have all the notes information in the tab "notes-MyAll". What I have to do is to update the *Sold* tab in the following steps soon after I sold notes:
1. get Note ID from Sold in this Month section in My Account in Foliofn.
2. search this Note ID in the tab "notes-MyAll", then cut the line of data and paste in tab "Sold"
3. Update the new row in *Sold*. Update the fields like principal remaining, interest received, status etc.
4. Copy it back to tab "notes-MyAll"
5. refresh the pivot.
Done.



rawraw

  • Hero Member
  • *****
  • Posts: 2794
    • View Profile
Re: Analysing performance of multiple portfolios in a account
« Reply #3 on: December 29, 2013, 11:10:36 AM »
I'm not following how I'm supposed to paste this stuff.  Am I supposed to paste special over the formulas? 

Edit: Think I figured it out, but the instructions should really be more clear Or those columns should be hidden.  But I still can't get it to work even though I now know where to paste.
« Last Edit: December 29, 2013, 11:12:59 AM by rawraw »

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Analysing performance of multiple portfolios in a account
« Reply #4 on: December 29, 2013, 12:36:46 PM »
Sorry it is difficult if you are not familiar with Excel worksheet. There are several places need to be updated to customize it for your account. I might add more explanation in revised version if my wife will not complain I spend too much time on LC. Here are some basic points behind the tool. Hope it can help.

The tab "notes-MyAll" has all notes information downloaded from LC on your "Notes" page. Download link is at the bottom of Notes page. Also, the left columns of this tab are all customized columns help to summarize notes data using Pivot tables in tab Analysis. You may change them and tables in tab Analysis tab for your own. The right part are notes data downloaded from LC. I always place on the top the notes sold in Forliofn (which is stored in tab Sold )

In the tab Analysis, one part is the two pivot tables at the bottom. They sumarize notes information using the columns I placed on the left side of the tab "notes-MyAll". The other parts are three tables extracting data from the two pivot tables, calculate and present them in a way I want. W.r.t the first table, you need to manually replace the portfolio name (2nd column) with yours. The numbers in the 1st column is used to group portfolios as whatever you want and they will be shown in the small table immediately below it.

Note: At any time you made any changes/updates in tab "notes-MyAll", click the shape on the top of tab Analysis will refresh the pivot tables, and hence refresh the three tables. If macro is disabled in your Excel spreadsheet, you may scroll down to the first pivot table, right-click on the table and select *Refresh*. It will do the same, i.e. refresh the pivots and tables.

The 2nd table is similar to the the first table, but I break the portfolio to loan grades and only examine two groups of my portfolio, group of models I am using now vs group of portfolios when I used LC filters to manually pick loans. The GrpId is customized for my this purpose.

The 3rd table is to provide more break-down analysis on the 4 models I am using now.
« Last Edit: December 29, 2013, 03:49:22 PM by RaymondG »

rawraw

  • Hero Member
  • *****
  • Posts: 2794
    • View Profile
Re: Analysing performance of multiple portfolios in a account
« Reply #5 on: December 30, 2013, 01:07:10 PM »
Sorry it is difficult if you are not familiar with Excel worksheet. There are several places need to be updated to customize it for your account. I might add more explanation in revised version if my wife will not complain I spend too much time on LC. Here are some basic points behind the tool. Hope it can help.

The tab "notes-MyAll" has all notes information downloaded from LC on your "Notes" page. Download link is at the bottom of Notes page. Also, the left columns of this tab are all customized columns help to summarize notes data using Pivot tables in tab Analysis. You may change them and tables in tab Analysis tab for your own. The right part are notes data downloaded from LC. I always place on the top the notes sold in Forliofn (which is stored in tab Sold )

In the tab Analysis, one part is the two pivot tables at the bottom. They sumarize notes information using the columns I placed on the left side of the tab "notes-MyAll". The other parts are three tables extracting data from the two pivot tables, calculate and present them in a way I want. W.r.t the first table, you need to manually replace the portfolio name (2nd column) with yours. The numbers in the 1st column is used to group portfolios as whatever you want and they will be shown in the small table immediately below it.

Note: At any time you made any changes/updates in tab "notes-MyAll", click the shape on the top of tab Analysis will refresh the pivot tables, and hence refresh the three tables. If macro is disabled in your Excel spreadsheet, you may scroll down to the first pivot table, right-click on the table and select *Refresh*. It will do the same, i.e. refresh the pivots and tables.

The 2nd table is similar to the the first table, but I break the portfolio to loan grades and only examine two groups of my portfolio, group of models I am using now vs group of portfolios when I used LC filters to manually pick loans. The GrpId is customized for my this purpose.

The 3rd table is to provide more break-down analysis on the 4 models I am using now.
I love Excel spreadsheets and was very disappointed when I had trouble.  I'll look at it more tonight as (you guessed it) I love excel spreadsheets