Attached to this message is a spreadsheet that is available to Zopa lenders as a demonstration of how to analyse their MLB using Microsoft Excel or another compatible spreadsheet. It provides numerous calculations and displays of useful data that can be derived from the MLB. This includes :
1) The easy way to get the latest MLB into your Excel workbook
2) An example Excel worksheet (2007 format). It has examples that show several Zopa MLB specific demonstrations that include:-
• Totals at each processing stage
• Total number of loans to unique borrowers
• Expected payments per month
• Accrual rate for interest and Zopa fee
• Average exposure per borrower
• GeoffJ maturity index for the MLB
• Average Return (pre Zopa fees and bad debt)
• Average Return (pre Zopa fees and bad debt) for each market / period
• Payments expected (today and 20 days forward)
• Graph - current loan market spread
• Graph - Expected payment per month for next 5 years
• Expected tax exposure for the remainder of this year and the next 5 years forward
• Breakdown showing the status of your capital for each month of investment with filter tools to allow you to select which markets and listings to include/exclude
• An estimate of your Zopa Return (post fees and losses)– the equivalent average percentage gain (pre tax) that you have achieved
• An estimate of what loans (by market) can potentially be sold via Rapid Return and what loans would be left
• Easy access to the recent comments that have been posted to your loans by the Zopa Admin team
The spreadsheet is presented with comments and annotations to help understand how it works. There are variables where you can customise it to your tax situation and data display preferences. It has NO visual basic or macros implemented, and so it should pose no security risks to your PC.
It has been tested with Excel versions 2003, 2007, 2010, Mac Excel 2008, OpenCalc, Chrome etc and appears to work with everything.
Please read the Getting Started notes that are included in the spreadsheet.
If there are any questions or your have ideas to improve it etc please post topics to the lender forum (thread for v 2.01 is here).
Replaced by v 2.02 - see the post below.
Edited by elljay, 17 July 2011 - 18:40.
Remove link to spreadsheet