Subscribe via RSS

Stay up-to-date on everything MFTransparency has to offer by following our RSS Feeds. You can follow all MFT updates, or select between just receiving only News or Resources updates.

Follow on Twitter

Follow the conversation: @MFTransparency

Like us on Facebook

Calculating Transparent Pricing Tool – v2.2

Published on September 22, 2012

Available Downloads

Languages available: English French Spanish Portuguese

MFTransparency’s very popular “Calculating Transparent Prices” Tool is an Excel-based tool that can be used to analyze the cost of a particular loan product. This tool helps the users understand how various factors influence the total cost of a loan. It also enables cost comparisons between loans with different fee structures. The software is multi-lingual and may be configured on the opening screen.

The three main components of the Calculating Transparent Prices Tool are:

  1. APR Calculator, Simple Approach: This page serves as a good starting point to learn the basics of pricing factors.  By inputting some commonly-used pricing factors, the software shows the APR at several different levels.  The APR is the “Annual Percentage Rate”, US standard for transparent pricing.  It is the most commonly quoted price in microfinance because it is close to the “portfolio yield” for the product.
  2. APR / EIR Calculator, Advanced Approach: This next section (pictured below) offers many more input options.  It also calculates both the APR and the EIR.  The EIR, or “Effective Interest Rate” is the approach used in most countries outside of the US that have legislation on transparent pricing, such as the European Union, Mexico, Peru, and Bosnia.  The Advanced sheets also have a manual-input option, including an advanced calendar function allowing the user to put in the exact repayment schedule (precise dates and precise amounts), allowing the user to calculate the exact price of the loan.
  3. Cost Structure Data sheet: The final section of the tool provides several worksheets to help understand the challenge of keeping costs low for micro-loans.  It allows the user to enter estimated delivery and maintenance costs for a specified range of loan amounts. This data is used to produce graphs and calculate a breakeven point.
“Our clients, who are the poor people that we’re working for, need to understand what they are paying for. This is a matter of trust. If we lose that it would mean that microfinance would miss its point, and I think that would be the end of microfinance in the way we want it to be.”
Christian M. X. Loupéda, Director – Credit with Education, Freedom from Hunger

 

17 Comments

  1. SAHADEV says:

    Thanks for the pricing tool. Excellent effort . Will go a long way in better pricing of loan products

  2. Otto González says:

    Thank you Chuck and MFT Team! I am remembering the MIcrofin´s course in Antigua when you was presenting the first version in English!

  3. daniel says:

    a very good tool for the purpose

  4. EBENEZER E. ARTHUR says:

    I am very much grateful for this MFT- Tool because it is actually happen. Thanks Almighty God bless you all.

  5. HNERY says:

    Thanks, I’m going to check, I’m sure will be very helpful … Greetings from Peru

  6. Joachim Bald says:

    Why does this transparency tool have to be so intransparent and overly complicated? It is really not that hard:
    1) Establish the required cash flow profile from the perspective of the borrower. In order to get 1,000, I have to pay you back how much and when?
    2) Take the internal rate of return for the period increment with Excel functions =IRR() or =RATE().
    3) Correctly annualize the period effective rate by going (1+period rate)^(periods per year) – 1.
    Finito!

  7. Chuck Waterfield says:

    Hello Joachim,

    I’m the developer of the intransparent, complicated tool. :)

    I find that most people don’t know the concepts and formulas. I used to say “Create a cash flow and use IRR” but most people haven’t done that. I developed versions of this that do the work for them, and they are grateful.

    I don’t believe the tool is intransparent. I show the process and the formulas, step-by-step on the RepSchedule pages. I hope people do go and study and learn from those formulas. Do go take a look, and you’ll see that I lay out the very steps of the formula you describe in your comment.

    I provide a variety of input cells to reflect the way prices are charged. I use that to generate the cash flow. This saves a great deal of time over trying to construct such a cash flow manually, as your comment suggests they do.

    I also use the XIRR function, rather than the IRR function you propose. IRR assumes exactly equal payment periods, and this is not always the case with loans.

    Finally, the software is free of charge. Criticism may be more valid if we were selling something that does a process that could be done simply without that product. Neither point is true in this case.

    Regards,

    Chuck Waterfield
    CEO, MFTransparency.org

  8. Joachim Bald says:

    Dear Chuck and MF Transparency Team,

    I apologize for my post of 10 Jan being a bit radical, maybe. Heat of the moment!

    I played with your tool some more and you are right. It is good stuff. Never doubted that. My concern is simply that the more tools we provide, there is always a risk we create more black boxes. When I work with people in the field, I always like to bring it back to the basics that can be explained in a quick Excel exercise.

    I wish you good luck and continued success with the Microfinance Transparency campaign. I share your passion about honest disclosure of the cost of microcredit and about bringing it down through technology, efficiency and scale.

    Regards,

    Joachim Bald

  9. COLLINS says:

    Thanks Chuck and your team. Please how do I create a currency symbol if the pre-defined ones do not relate to my country?
    Thanks.

    • Chuck Waterfield says:

      Hello Collins,

      Move to the right of the currency dropdown about 5 columns…you’ll see three lines where you can add and customize the currency options that will show up at the bottom of the currency dropdown list. Do read the pop-up help you’ll see in those grey cells.

      Also, note that the currency symbol does nothing to the calculations. It only makes the screen prettier, but the math is exactly the same independent of what currency is chosen.

      Chuck Waterfield

  10. Liam Sharp says:

    Dear Mr Chuck Waterfield,

    Thank you for the provision of this tool – I am finding it very useful.

    A quick question – is it possible, using the tool, to set interest repayment amounts for a loan to find the requisite interest rate, APR and EIR?

    For example for a loan amount of $525 I would like to set monthly repayments of $50 dollar over a year and to find out what interest rate, APR and EIR this equates to.

    Essentially trying to design loans with very simple repayment amounts (to avoid decimals etc).

    Many thanks and best regards
    Liam

  11. santiel says:

    I want to calculate APR and EIR for my institutions products, Kindly assist

  12. Chuck Waterfield says:

    Hello Santiel,

    The software on this page will help you learn about pricing and calculate your prices. We have additional educational materials elsewhere on our website. After you’ve reviewed these, if you still have questions, you can email us at info@mftransparency.org and we can help you out.

  13. Dear Mr. Chuck Waterfield,
    Greetings from Nepal!!!
    Thank you very much for your great efforts to develop this excellent tool. It really clarifies to borrowers on how much they cost on particular loan.
    We have provision of interest rebate (return 5% of paid interest amount when the loan cleared) on good loans. I would appreciate if you could incorporate the provision of interest rebate options in the sheet.
    Thank you,

  14. Andrew Pospielovsky says:

    Hello Chuck,

    I have been meaning to thank you for along time. As a Consultant I have been using your tool for years now on dozens of projects. It still surprises me how often the management of institutions do not understand the actual cost of their products and how often this results in huge inconsistencies in their pricing models. While the model can take some time to get used to, it is very powerful in being able to factor all costs and demonstrate clearly to the client the contribution of each factor to the total cost. Some clients do not believe the figures until they compare the actual repayment table generated by the calculator against their own tables – and this again is of huge value.

    This is the most valuable tool I use in my work.

    Warmest regards,

    Andrew

  15. joe bochi says:

    Thank you very much… very useful indeed. Is there a way currency can be changed? or the template doesn’t allow editing…

    Anyway, I’m grateful…

    • Chuck Waterfield says:

      Hello, Joe

      The currency symbol can be changed using the dropdown list on the top-right of the opening worksheet. If the currency symbol you want doesn’t appear on the list, there is an area to add more symbols further to the right. Please note that the price calculation does not vary because of the currency. This currency symbol is nothing other than a symbol, so that the loan looks like it is in your currency. The same APR results if the currency is USD, Euros, or Shillings.

Leave a Reply