# Calculating Transparent Pricing Tool – v2.2

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:

**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.**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.**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*

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

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

a very good tool for the purpose

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

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

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!

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

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

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.

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

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

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

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.

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,

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

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…

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.

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

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

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

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.

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.

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

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

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