Analysing performance of multiple portfolios in a account

[RaymondG]

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" class="bbc_link" target="_blank">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.

[Zach]

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?

[TravelingPennies]

https://forum.lendacademy.com/index.php?topic=1908.msg15831#msg88888888Quote"> from: Zach on December 29, 2013, 01:09:46 AM

[rawraw]

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.

[TravelingPennies]

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.

[TravelingPennies]

https://forum.lendacademy.com/index.php?topic=1908.msg15846#msg88888888Quote"> from: RaymondG on December 29, 2013, 12:36:46 PM