Summary spreadsheet

[Guest]

.

[YowieFreak]

This spreadsheet allows you to easily manage and make sense of your Kiva portfolio, whatever its size.


Attached are two versions of the spreadsheet - v2.05 uses msxml.dll, and v2.05.6 uses msxml6.dll.

I recommend people use v2.05.6 unless they don't have msmxl6.dll installed on their computer and aren't willing to install it (e.g. on company computers, etc).

[YowieFreak]

I have moved the summary spreadsheet into a new topic, to make it easier to link to from Kiva (and anywhere else).

Version 2.00(.6) has the following modifications from what was in v1.99(.6):

• Statistics based on currency risk status are now shown on the Stats tab.  (So you can see how much of your portfolio is at risk.)
• When determining when repayments are expected the macros will now correctly handle loans which aren't funded until after the MFI would normally have reported to Kiva the first set of repayments from the entrepreneur.  (After I made this code change, Kiva also changed their repayment schedule information to cover this situation, so my changes are probably pointless now.)

P.S.  I intend to keep the most up-to-date version of the spreadsheet attached to the first message in this thread.

[JoanW]

I think this is a good way for others to find it.  Could you put a line in that first email with a "date of last update" somewhere? I may/may not remember the version number, but I would know by the date.

And if I haven't said it recently - THANK YOU for the spreadsheet.

[wthepoo]

Ian,

noticing some inconsistencies between my "Summary" and Kiva's database, I looked a little closer and noticed three things:

* "Kiva loan data" - Kiva's API seems to return the 16th of a month instead of the 1st as "Date First Repayment Due". Is there any apparent reason for this or do the engineers simply don't know or care that the Due Date has been shifted forward?

* "Loans" - The "Expected Paid Back" today seems to already include what is expected to be returned on the 15th of this month (or am I imagining things?). Is there any reason for this?

* "Loans" - This is just in case you want to get your spreadsheet's results regarding delinquencies completely in synch with Kiva's database: Kiva uses some sort of minimal rule for determining whether a loan is delinquent (IIRC we asked them to implement this to avoid penny delinquencies boosting the delinquency rate and causing concern), your summary spreadsheet does not (which is OK, I just noticed it today). I currently have 7 such "false positives" (delinquent loans that are not marked "delinquent" by Kiva), in 6 cases due to small amounts underpaid in general (between $0.01 and $0.14, based on the total repayments, between $0.01 and $0.03 regarding my $25-share), in 1 case due to a "penny error in the making" (loan export reports correct repayments, transaction export reports $0.03 missing).

Just in case you are interested - the first 6 loans are: 91319, 102430, 91122, 89423, 89294, 84021; the 7th loan is: 62065


As always: many thanks, and best wishes,
Wolfgang.

[tomviolence]

hi

tried out new version. thanks again for all your work.

I noticed >> PROI = ( R - NI ) / NI 205.94% which is a bit funny.

On 7-8 when I ran it I got PROI = ( R - NI ) / N 110.40%   

I think the gift certificates I bought in the interveneing time make the numbers go funny.

Since they subtracted from deposits, you could end up with a zero denominator.

Possible ideas for the "Fun Stuff" page -  Loaned / Deposited , this will show your multipleir value (How many times your $1 has been reused )

[YowieFreak]

I noticed >> PROI = ( R - NI ) / NI 205.94% which is a bit funny.

On 7-8 when I ran it I got PROI = ( R - NI ) / N 110.40%   

I think the gift certificates I bought in the interveneing time make the numbers go funny.

Since they subtracted from deposits, you could end up with a zero denominator.

You should only end up with a zero denominator if you have managed to use all the money you have ever invested into Kiva in order to buy GCs.  (Possibly after having put it through a few loan cycles first.)  That would mean that you have no funds left in Kiva credits and no outstanding loans, which probably means you aren't going to be using the spreadsheet.

(I think that's true anyway - unless I am missing something.)

[YowieFreak]

* "Kiva loan data" - Kiva's API seems to return the 16th of a month instead of the 1st as "Date First Repayment Due". Is there any apparent reason for this or do the engineers simply don't know or care that the Due Date has been shifted forward?

Is that happening on all your loans, or only on the ones prior to Kiva changing to a 1st of the month due date?

* "Loans" - The "Expected Paid Back" today seems to already include what is expected to be returned on the 15th of this month (or am I imagining things?). Is there any reason for this?

It looks like I haven't correctly handled the fact that there is now a period between the previous due date and the next "expected" repayment date.

In the old days we would expect the repayments on the 15th of one month for amounts due on the 15th of the next month, so it was just a case of saying that everything that was due up to the next due date was currently "expected".  Now it appears that I should be saying that that is only the case between the 15th and the end of the month.  Between the 1st of the month up until the 14th only the amounts due up to the previous due date are "expected".

Looks like v2.01 will have to come out soon. 

* "Loans" - This is just in case you want to get your spreadsheet's results regarding delinquencies completely in synch with Kiva's database: Kiva uses some sort of minimal rule for determining whether a loan is delinquent (IIRC we asked them to implement this to avoid penny delinquencies boosting the delinquency rate and causing concern), your summary spreadsheet does not (which is OK, I just noticed it today). I currently have 7 such "false positives" (delinquent loans that are not marked "delinquent" by Kiva), in 6 cases due to small amounts underpaid in general (between $0.01 and $0.14, based on the total repayments, between $0.01 and $0.03 regarding my $25-share), in 1 case due to a "penny error in the making" (loan export reports correct repayments, transaction export reports $0.03 missing).

I think it will be almost impossible to match exactly what Kiva does.  For instance, if they say that a loan is not delinquent if the repayments are no more than $1.00 behind and a particular loan is $1.01 behind, then some lenders (if there are more than 100 lenders on the loan) may have received 100% of their expected repayments even though Kiva would be showing that the loan is delinquent.  And if the loan was only $0.98 behind, but there were only 2 lenders, then each lender could be missing $0.49 while Kiva would be showing the loan isn't delinquent.

Very complicated - so I think I will leave it until we find out exactly how Kiva determines whether the loan is delinquent.  (Which will probably be never )

[wthepoo]

Is that happening on all your loans, or only on the ones prior to Kiva changing to a 1st of the month due date?

My mistake - sorry... wasn't paying enough attention. (Though it still seems to be strange that it's the 16th instead of the 15th IMHO.)

It looks like I haven't correctly handled the fact that there is now a period between the previous due date and the next "expected" repayment date.

In the old days we would expect the repayments on the 15th of one month for amounts due on the 15th of the next month, so it was just a case of saying that everything that was due up to the next due date was currently "expected".  Now it appears that I should be saying that that is only the case between the 15th and the end of the month.  Between the 1st of the month up until the 14th only the amounts due up to the previous due date are "expected".

I agree.

Looks like v2.01 will have to come out soon. 

Sorry. 

I think it will be almost impossible to match exactly what Kiva does.  For instance, if they say that a loan is not delinquent if the repayments are no more than $1.00 behind and a particular loan is $1.01 behind, then some lenders (if there are more than 100 lenders on the loan) may have received 100% of their expected repayments even though Kiva would be showing that the loan is delinquent.  And if the loan was only $0.98 behind, but there were only 2 lenders, then each lender could be missing $0.49 while Kiva would be showing the loan isn't delinquent.

Very complicated - so I think I will leave it until we find out exactly how Kiva determines whether the loan is delinquent.  (Which will probably be never )

I agree, just wanted to mention it at least. It might still be a thought, though - for version 3.0 or later -, to read out Kiva's delinquency status and go for a different colour-coding (red for loans that are delinquent for you and for Kiva, purple for loans that are delinquent only according to Kiva but not according to your spreadsheet [seems unlikely to me unless in rare cases of reverse penny errors on loans which just reach Kiva's delinquency threshold], orange for loans that are delinquent only according to your spreadsheet but not according to Kiva... or any other colour combination or none of the above).

As always, many thanks for the spreadsheet(s) and all your work, best wishes,
Wolfgang.

[YowieFreak]

My mistake - sorry... wasn't paying enough attention. (Though it still seems to be strange that it's the 16th instead of the 15th IMHO.)

It actually shows via the API as 8am on the 16th UTC (or 7am on the 16th UTC when California is on PDT time) - so that makes it the very end of the 15th Kiva-time - which is what I had always understood the due date/time to be based on when I had historically seen loans becoming delinquent.

I have also always considered it weird that the due date was changed to be the 1st - I would have expected that Kiva would have given MFIs to "the end of the month" to make repayments but they have actually given them to "the end of the 1st day of the following month".  Just doesn't seem "symetrical" to me.

[wthepoo]

Thanks for the clarification, Ian - very interesting.

Best wishes,
Wolfgang.