Portfolio overview - accumulated cash-flow (repayment schedule) of all loans

[Guest]

.

[eagle-1964]

Hi, I am new to Kiva and I really like the concept.
However on my 'my portfolio' site, I would like to have additional information, which could be helpfull for everyone.

For example, it would be helpfull for my planning of further loans to accumulate the expected payment schedules into one common cash flow schedule.

This would give me the information on the currently outstanding loans and I could also better plan how to reinvest (lend) the returned loans (payments).

The sum of the the balance (available KIVA credit) and the outstanding loans would give me the value of the portfolio (respectively the amount of money I have dedicated for investing in Kival loans).

Maybe this could be relatively simple partialy integrated in 'my loans', where we already have the columns 'you loaned' and 'paid back'(today)  -- paying back next month, paying back next quarter, etc. for each project and/or accumulated repayments at the end of the page similar to the repayment schedule view on the lenders page.

[cjp1973]

Welcome to kivafriends eagle-1964

While many of us here agree that there needs to be some improvements in ways to manage your portfolio from the kiva site,  Kivafriend YowieFreak has developed a great tool to do this.  Here is the link to the Summary Spreadsheet and some information on it.  Please feel free to post more questions and someone will for sure answer them.

http://www.kivafriends.org/index.php/topic,4176.0.html

[wthepoo]

Just adding my welcome, eagle-1964, and seconding Charmaine's recommendation.

The Excel Summary Spreadsheet has an own tab "Cashflow" that does just what you are asking for.

Best wishes,
Wolfgang.

[eagle-1964]

Thanks for the fast info.
I have tried the excel sheet, which is nice, however my loans are not correctly applied, since they have no linear payback (the info from the export file is only given in how many month, but not the payment schedule) -some have increasing payments and others pay each quarter (however this data is not available from the export file only on the repayment schedule on the project page ->  so it should also be possible to export it).
-> because of that one of the loans becomes delinquent, even so it is not.

So I had entered them manually, which is possible, however after recalculating (press me - button) the linear payments are back.
Anyway it is a good start for most issues, but I will probably modify somethings for myself or wait for some more complete export data from Kiva...this would be best

Thanks, I look forward to actively participate in KIVA.
Frank

[YowieFreak]

Thanks for the fast info.
I have tried the excel sheet, which is nice, however my loans are not correctly applied, since they have no linear payback (the info from the export file is only given in how many month, but not the payment schedule) -some have increasing payments and others pay each quarter (however this data is not available from the export file only on the repayment schedule on the project page ->  so it should also be possible to export it).
-> because of that one of the loans becomes delinquent, even so it is not.

So I had entered them manually, which is possible, however after recalculating (press me - button) the linear payments are back.
Anyway it is a good start for most issues, but I will probably modify somethings for myself or wait for some more complete export data from Kiva...this would be best

Thanks, I look forward to actively participate in KIVA.
Frank

Veeery strange!   

The macros in that spreadsheet go and download the exact repayment schedule for each loan from Kiva, so they should be showing exactly what is shown on Kiva's repayment pages.

Can you go to the "Kiva Loan Data" tab, pick one of the loans that is showing a linear payback and which you believe shouldn't be, and let me know the business id.  I'll investigate further to see if I can spot what's going on.

Regards,
  Ian

[eagle-1964]

Hallo Yowie,
thanks for your reply - I will send you my data sheet.

I have for example lended to the following projects/entrpreneurs:
132754   Angel   $1,000.00
the repayment schedule on the website is (starting Nov. 09, each month):
 $105,78     $110,70     $117,05     $122,32     $126,99     $132,90     $140,39     $143,87
the data from 'loans' -> export is:
132754   Angel   $1,000.00   10 months Aug 18, 2009   Peru  Microfinanzas Prisma      $25.00   $0.00   Paying Back

121827   Las Rosas Group   $4,550.00
the repayment schedule on the website is (starting Oct. 09, each month):
1820,23    $757,75     $775,30     $793,24     $403,48
the data from 'loans' -> export is:
121827   Las Rosas Group   $4,550.00   8 months Jun 19, 2009   Bolivia  Fundación AgroCapital, a partner of ACDI/VOCA      $25.00   $0.00   Paying Back

126671   Goshgar Azizov   $2,500.00
the repayment schedule on the website is (starting april 10, every 3 month, linear):
Apr.10  $500,00
Jul. 10  $500,00
Oct. 10 $500,00
Jan. 11 $500,00
Apr. 11 $500,00
the data from 'loans' -> export is:
126671   Goshgar Azizov   $2,500.00   20 months Aug 01, 2009   Azerbaijan  Komak Credit Union      $25.00   $0.00   Paying Back

I add an excel file with my 'portfolio', the manually entered loans (copy&paste from the payment schedule on the project page) and the expected 'cash-flow'.

From the data recieved from the general export of the 'Loans' page, i don't believe it is possible to retrieve sufficient info to import the correct payment schedule into the tool. However the data must be available in the website data, otherwise it couldn't be shown.

Frank

PS:
PS I wanted to attach the file, but got the message the upload folder is full - so I will try to send you the file direct (38 KB)

[wthepoo]

From the data recieved from the general export of the 'Loans' page, i don't believe it is possible to retrieve sufficient info to import the correct payment schedule into the tool. However the data must be available in the website data, otherwise it couldn't be shown.

Hallo Frank,

you are right - the exports (Loans and Transactions) aren't sufficient for the Summary spreadsheet macro to create an accurate repayment schedule. But the current version of Ian's Summary spreadsheet macro also extracts live loan data from Kiva's API and should thus - as Ian described - populate the "Kiva loan data" tab with the actual scheduled repayments and use this information to calculate the Cashflow and all further loan and partner stats.

I think Ian wanted you to provide him with the contents not of the "Loans" export but of this "Kiva loan data" tab of his spreadsheet after a complete run-through of the macro. (Please make sure that the number you enter in cell B6 of the "Instructions" tab is higher than the number of your loans, at least for the first complete run-through.)

Btw, seeing that you are from Germany: I also offer full German language support for Ian's spreadsheets; just send me a message with any further questions. 

Viele Grüße,
Wolfgang.

[eagle-1964]

Hallo Wolfgang,

Thanks for the offered help, i will get in touch with you also on direct email.

Frank

PS:
I am trying again to upload the sheet with my loan data.