Excel spreadsheets - Would you use one if it used macros to do the work

[Guest]

.

[wthepoo]

I ran the newest just now and am now down to 3 delinquent on the same portfolio (more or less). . .

I think I have found one error though - I have two lines in my repayment that reads "Catch-up for penny errors (ids=29900,38621,40915,45451,46049,46050,46051,46469)." and "Catch-up for penny error." for 6 pence and 1 pennny.

These 6 pence match exactly Written off in the Summary tab in my copy.  Not a huge sum but apparantly a glitch in you spreadsheet I presume. . .

As I understand you stretch for 100% perfect I mention it here:)

-- Dagfinn A

Not a glitch in the spreadsheet, Dagfinn, but a glitch in Kiva's accounting made good by these penny repayments. Because in the first case one combined transaction is attributed to several loans, the spreadsheet - understandably - cannot deal with it on its own.

You will thus have to adjust for it on the "My extra transactions" tab and attribute the cents/pennies to the respective loans.

I had a transaction "Catch-up for penny errors (ids=51561,53367,68396)." crediting me $0.01.

From the "loans" tab, I could see that with businesses 51561 and 53367, I had received only $24.99, while with business 68396, I had received $25.01.

This translates to these "extra transactions":

Amount	Date	Type	Business ID	Comments
-0.01	May 13, 2009	Loan Repayment	68396	penny error
0.01	May 13, 2009	Loan Repayment	51561	penny error
0.01	May 13, 2009	Loan Repayment	53367	penny error

I hope this is helpful for you to figure out your "extra transactions".

Best wishes,
Wolfgang.

[Dagfinn]

I do - thank you Wolfgang 

-- Dagfinn A

[Dagfinn]

Just one problem Wolfgang - they are all exactly correct, no pennies missing or too many anywhere . . .

Any more clues?


-- Dagfinn A

[wthepoo]

Not really Dagfinn,

just the advice to check again:

Ian's spreadsheet, tab "Loans", columns X (Paid Back) and Z (Paid Back discrepancy).

If indeed for these loans the Z column shows no entry, I don't know.

Best wishes,
Wolfgang.

[Dagfinn]

ooops - I looked in the My loans tab     the Loans tab show 20 cents from 33 loans.  Anyway, go to sleep while I debate with my brains and I sent you the data as you suggested.


-- Dagfinn A

[YowieFreak]

... and I sent you the data as you suggested.

I've looked at the data you sent.

Catch-up pennies
The 6 "catch-up" pennies should be applied to the 9 loans that Kiva had previously marked "Paid Back" but with amounts paid-back not equalling the amount loaned.  (Refer Wolfgang's post.)  Two loans had been overpaid by 1 cent, six had been underpaid by 1 cent, and one had been underpaid by 2 cents.

Delinquencies
According to the spreadsheet, there are 6 delinquent loans in your portfolio.  The earliest three (i.e. the three where you have received some repayments so far) are "real" delinquencies, and are shown on Kiva as such.

Two of the remaining loans (#114322 and #115131) weren't fully funded until earlier this month, therefore any repayments that the MFI has received on them won't be reported back to Kiva until early next month, therefore the first credit that we can reasonably expect for them will be (hopefully) received on August 15 (but not theoretically due until September 1).  So, although the spreadsheet thinks they are delinquent, Kiva knows that it isn't reasonable to class them as such yet.

The final loan (#105091) still isn't funded but (assuming it does get funded and doesn't expire) it will end up being in the same situation as the other two.

These three loans are being treated incorrectly by the spreadsheet simply because the spreadsheet doesn't handle the fact that loans might be funded after the first payment is due from the entrepreneur to the MFI.  I'll hopefully fix this soon.


I'll email you back a copy of the spreadsheet so that you can see exactly how I entered the extra pennies on the "My extra transactions" tab.

[AccountAbility]

I've looked at the data you sent.

Catch-up pennies
The 6 "catch-up" pennies should be applied to the 9 loans that Kiva had previously marked "Paid Back" but with amounts paid-back not equalling the amount loaned.  (Refer Wolfgang's post.)  Two loans had been overpaid by 1 cent, six had been underpaid by 1 cent, and one had been underpaid by 2 cents.

Since I've been waiting for the pennies issue to resolve, are you saying that these pennies are actual transactions flowing through the transactions record-- or are they just a spreadsheet mechanism to handle the "overs and shorts" due to Kiva's penny rounding issue?

Dan

[YowieFreak]

Since I've been waiting for the pennies issue to resolve, are you saying that these pennies are actual transactions flowing through the transactions record-- or are they just a spreadsheet mechanism to handle the "overs and shorts" due to Kiva's penny rounding issue?

Dan

These are actual transactions that appear in some users' transaction export files.

I think they are manually created transactions because they often correct multiple loans in the one transaction (one transaction for Dagfinn resolved the overs/unders of eight different loans, the other transaction he had was for a single loan), and they often have differing formats for the description and even transaction type (whereas an automated process would be consistent).

Edit:  And because they are manually-created / inconsistent, the spreadsheet usually doesn't know what to do with them when it encounters them in the transaction export file, so it ignores them and the user has to manually enter the correct info regarding them in the "My extra transactions" tab.

[Dagfinn]

I've looked at the data you sent.

Catch-up pennies
The 6 "catch-up" pennies should be applied to the 9 loans that Kiva had previously marked "Paid Back" but with amounts paid-back not equalling the amount loaned.  (Refer Wolfgang's post.)  Two loans had been overpaid by 1 cent, six had been underpaid by 1 cent, and one had been underpaid by 2 cents.

Delinquencies
According to the spreadsheet, there are 6 delinquent loans in your portfolio.  The earliest three (i.e. the three where you have received some repayments so far) are "real" delinquencies, and are shown on Kiva as such.

Two of the remaining loans (#114322 and #115131) weren't fully funded until earlier this month, therefore any repayments that the MFI has received on them won't be reported back to Kiva until early next month, therefore the first credit that we can reasonably expect for them will be (hopefully) received on August 15 (but not theoretically due until September 1).  So, although the spreadsheet thinks they are delinquent, Kiva knows that it isn't reasonable to class them as such yet.

The final loan (#105091) still isn't funded but (assuming it does get funded and doesn't expire) it will end up being in the same situation as the other two.

These three loans are being treated incorrectly by the spreadsheet simply because the spreadsheet doesn't handle the fact that loans might be funded after the first payment is due from the entrepreneur to the MFI.  I'll hopefully fix this soon.


I'll email you back a copy of the spreadsheet so that you can see exactly how I entered the extra pennies on the "My extra transactions" tab.

Tusen takk Ian - Iam no especially good at this so I do look forward to get a corrected copy of you 

-- Dagfinn A

[JoanW]

I quit updating the spreadsheet several versions back. I was happy with how it worked, and I really did not need the level of detail that was being talked about here in the forum. Having just updated to the newest (?) version 1.99 I am glad I did. It is really amazing.

Ian, thank you so much for all your hard work.

Also my thanks to those who helped along the way testing & tweaking.

It's a great tool.