Notice: MFTransparency is now a defunct organization. Click here for further information.

Calculating Transparent Pricing Tool – v3.0

Published on September 22, 2012

Available Downloads

Languages available: English

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.
  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.
  4. Exact Pricing:  Version 3.0 of the tool includes an additional sheet that is similar to the Advanced Worksheet but includes additional ability for manual-input of all dates and amounts used to generate the true prices.  If you have a real loan repayment schedule, this worksheet will calculate the exact price.
“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

 

33 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 [email protected] 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.

  16. Patrick AMVELLA says:

    Hello Chuck,

    Thank you for this great application. I wanted to customize it but I realized that it requires a password. So, I would like to know whether it is possible to have the password that will give me the possibility to adjust some cells in the file.

    Thank you in advance.

    Best,

    Patrick

    • Chuck Waterfield says:

      Hi Patrick,

      The formulas are not easy to modify, and you can have unintended consequences. What our policy has been is that we welcome ideas for improvement and new features. In most instances, I can work those changes into the file quickly (I am the developer of the software), and then the new features work without breaking the software… and they are still available for you (and others) when new versions of the software come out.

      Feel free to email me directly with the ideas you are considering and I’ll let you know if we can implement them.

      Chuck Waterfield
      CEO, MFTransparency

  17. Teddy Lim says:

    Hi.. i cant find a link for downloading this tools. can you suggest me the link how to download. thanks.

  18. Chuck Waterfield says:

    Hello,

    The box on the top left that says “available downloads” has flags for four different languages. Click one of those and the download starts.

  19. Derek says:

    I am curious about one seemingly minor issue – why does the Advanced Pricing tool omit the insurance fees for the final payment? At least to my thinking the insurance fee should also apply to the final payment, to cover the period of coverage between the penultimate payment and end date of the loan. Otherwise, the cost of the insurance becomes slightly less transparent. In any case, is there an easy way to change this? I don’t have a password to unprotect the sheet and change the formula.

  20. Derek says:

    Another question – why does the “equal installments” options calculate those equal installments as pre-tax and not post-tax?

  21. Chuck Waterfield says:

    Hello Derek,

    Thanks for your interest in the tool. On your first question, about insurance, the tool DOES charge insurance for every payment, including the last. I assume you have an “ongoing insurance” and whether it is a fixed amount or a percentage, it does appear for every payment. If you still see a problem, email us a picture and we’ll investigate.

    On your second question, the calculator uses the Excel financial functions that blend principal and interest and make an equal installment. If you charge a fixed fee every month, the installment will still be equal. If you charge a % fee each month, the installment will vary. Same with taxes, or insurance… if the monthly amount of taxes or insurance varies, the total amount the client pays will vary. It would take a complicated formula that Excel doesn’t have to make things equal for all the different components. However, the impact on the APR is very, very small, unless the tax figure is very, very large. The margin of error here is very likely negligible.

    Let us know if you have other questions.

    Chuck Waterfield

  22. Hans Henrik Hammerum says:

    Above Joachim Bald has a comment:

    3) Correctly annualize the period effective rate by going (1+period rate)^(periods per year) – 1.

    As I can see your spreadsheet does not use the formula suggested by Joachim which I find to be the correct way to calculate in line with the FCA guideline on calculating APR (MCOB 10.3).

    I could not find your response to this part of his post. I think it is crucial since using this definition values become different and then we miss what is all about. If we do not agree how to calculate we cannot compare.

    Best regards
    Hans Henrik

  23. Chuck Waterfield says:

    Hello Hans,

    What you and Joachim were referring to is a difference between two approaches for annualization of pricing data. In the EU, the process is compounded, using the formula you refer to. In many places, like the US, the process is nominal annualization.

    MFT refers to the nominal annualization as APR and the compounded approach as EIR.

    The Price Calculator Tool provides both prices on the ADVANCED sheet.

    I agree with you that a standardized definition is crucial so that we can compare figures. The Industry (and MFT) have standardized on using the APR approach. All figures shown on the website are APR. You will find some PPTs and articles on the MFT website that explain our rationale. But in short, when running the numbers you will find that 10% per month (like you will find in many countries on the MFT website) results in 120% APR and 255% EIR. Portfolio yield for that MFI is approximately 120%, not anywhere near 255%. Nobody but a small percentage of financial analysts can understand or explain why 10% a month becomes 255% per year. You’ll also note that we calculate an MPR in the Price Calculator, as one approach that gets beyond this bewilderment.

    Chuck Waterfield

  24. Cesar says:

    I down loaded this version today and in the Rep Schedule – Advanced, there used to be a manual-input box to check, it does not show now. need help.

    • Chuck Waterfield says:

      Version 2 had a manual option that was difficult to use. Version 3 has a new worksheet following “advanced” where you have 100% control over all the dates and amounts. This sheet is what you should use to calculate manually.

  25. Hong Ry says:

    Dear developer,

    I found this tool really useful in calculating EIR but I did know why it could not do manual calculating as v2.22?

    Because some borrowing fund are required different repayment mode like pay principle as mid of the term or par principle with different amount….

    • Chuck Waterfield says:

      Version 2 had a manual option that was difficult to use, but it did still work, and you could do what you are describing. Version 3 has a new worksheet following “advanced” where you have 100% control over all the dates and amounts. This sheet is what you should use to calculate manually to have full control over the dates and payments made in the loan contract.

  26. alba says:

    Dear Mr. Chuck,
    I found it quite useful.
    is it in portuguese too?
    thanks

    • Chuck Waterfield says:

      Yes, the one software file has four languages – English, Spanish, French, Portuguese. You can choose the language on the first sheet when you open the file.

  27. Glendon says:

    It would be great if you could include “week day payments”

    Most alternative lenders process payments on weekdays which makes for complicated amortization schedules.

    Including weekday payments with the XIRR calculations renders APR’s much higher than what are being quoted to borrowers.

    This would address many situations that people are in, especially here in the US.