5 replies to this topic

[Geoffj]

Hi

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.

Geoffj

Edited by elljay, 17 July 2011 - 18:40.
Remove link to spreadsheet

[Geoffj]

Hi,

Just to confirm that version 2.02 is now the released version and this replaces all previous releases.

Version 2.02 is an improvement over 2.01 as it:
1) Fixes a bug in the RapidReturn loan logic - it will now deal correctly with the scenario where you sell loans and/or acquire loans via RR that you already have.
2) Significantly improves the #10 Loan Comments area. There are settings on the variables so that you can tune what you see (or not)

Replaced by ver 2.10 - see the post below.

Regards,
Geoffj

Edited by elljay, 17 July 2011 - 18:41.
Remove link to file

[Geoffj]

Hi

I am pleased to release ver 2.10 of the MLB demonstration spreadsheet.  This release replaces all previous versions.

There are no critical bugs in the previous version, so don't feel compelled to use version 2.10.  

Version 2.10 provides a few enhancements that may be of interest:-

1) A "front page" has been implemented as suggested by GG.  If anyone has views on this tab we can develop it further over the next releases.

2) All "closed loans" can not be collapsed into a single loan to greatly reduce the number of unique loans that you need to work with.  If you have a large number of closed loans and are just using the spreadsheet to look at future payments and the like, then this will improve the responsiveness considerably.

3) A mini cash-flow statement is provided.  This works because of data that is now available in Zopa Return. If "Zopa Return" is not set-up, then this mini cash-flow statement will be wrong. (If it causes users any problems then I will consider removing it in a later release.)

The holiday list updated.

A discussion area has been created for any questions and suggestions for the MLB demonstration spreadsheet.  It is here.

DOWNLOAD VERSION 2.10 - Updated by version in following post

Regards
Geoffj

Edited by elljay, 03 May 2012 - 20:09.
Remove link to file - see next post

[Geoffj]

Hi

I am pleased to release ver 2.11e2003 of the MLB demonstration spreadsheet. This release replaces all previous versions.

This version provides some fixes and enhances.  You are encouraged to upgrade to ver 2.11e2003.  But please read the below carefully.

The fixes and enhancements since 2.10 are:-

1) Bug-fix - the loan breakdown tab failed to report loans when they are written on the last calendar day.  

2) Added a function to allow closed closed and/or active loans to be removed from the loan breakdown tab.

3) Added/enabled support for the 24 and 48 month loans.

4) The holiday list updated.

Version 2.11Excel2003 fully replaces the previous version.  All systems that ran a previous version of the spreadsheet should be upgraded to this version.

Shortly a version 3.00 will be released.  This version will be mostly compatible with Excel 2003 but will have a tab that uses Excel 2007 syntax formulas.  Users with Excel 2007/2010 will be able to run everything.  Users with Excel 2003 or compatible spreadsheet are advised to delete the tab(s) that use the Excel 2007 syntax.  Instructions will be provided with the v3.00 release.

Users with Excel 2007 or later may choose to wait until version 3.00 is available.

DOWNLOAD VERSION 2.11e2003 - removed - see newer version in later post

Regards
Geoffj

Edited by elljay, 08 July 2012 - 18:15.
Remove download link

[Geoffj]

Hi

Version 2.20 of the Demonstration MLB spreadsheet is now available

It is available removed - see later posts

This version provides the following key enhancements
1) Supports the new MLB file format for shorter/longer loans
2) Provides a new tab to allow impaired loan analysis.  (If you are new to Zopa and have a very young loan book then you can ignore this tab.   )

This version should replace all previous copies.

Regards
Geoffj

Edited by elljay, 26 August 2012 - 06:47.
remove link

[Geoffj]

Hi

Version 2.21 of the Demonstration MLB spreadsheet is now available

It is available here

This version fixes a few minor but very annoying bugs in version 2.20

This version should replace all previous copies.

Regards
Geoffj

Edited by elljay, 26 August 2012 - 06:46.
add link