I have to be honest. I had never heard of a flat rate loan, until Rishi asked me to create an Excel spreadsheet for one. According to Wikipedia, flat rate loans are:

“Loans with interest quoted using a flat rate originated before currency was invented and continued to feature regularly up to and beyond the 20th century within developed countries. More recently, they have also come to be used in the informal economy of developing countries, frequently adopted by microcredit institutions”.

In countries where most people do not have access to a calculator or a computer, flat rates simplify the loan payment and interest calculations. That’s not to say that people in developed countries necessarily understand declining balance calculations.

The calculation for monthly payments on a flat rate loan are as follows:

Principal Portion: Loan Balance/Months to Repay

Interest Portions: Flat Rate/12*Loan Balance

Monthly Payment = Principal + Interest

Principal, Interest, and Payments remain the same throughout the life of the loan. You enter the loan data in the yellow cells, in the spreadsheet called: Flat_Rate:

Comparing the flat rate to an amortized, declining balance loan rate, we need an APR. A rule of thumb, is that a comparable amortizing loan APR is about twice the flat rate. Using Excel’s IRR function we can get an exact APR, which shows that the **5.00%** flat rate converts to an **9.105%** APR:

Or, because the cash flow is constant, we can use Excel’s Rate function (see spreadsheet for actual formula inputs):

Obviously, a 5.00% flat rate is much more expensive than a 5% amortizing loan. In order to prove that, I have included an amortizing loan schedule. The schedule offer the user an option of using the flat rate as an input or the APR. Below shows the comparison using a 5.00% flat rate:

This shows that a 5.00% flat rate will cost the borrower an additional 11,053.46, compared to an amortizing loan. Notice that I did not use a $, since anyone interested in a flat rate loan will be most likely be using a different currency.

Changing the dropdown cell to 9.105%, shows that a 5.00% flat rate is equal to a 9.105% amortizing loan, with the present value of each being 500,000.00.

I also included a calculator to convert a Flat Rate to an APR or an APR to a Flat Rate. This could be helpful for those that want to compare loan rates:

Download: Flat Rate.xlsx

VinitHi, would like to ask, is there a formula in excel for computing a loan portfolio DCF? Let’s say my my loan portfolio has commenced 2 years ago, and come this year, I would need to value at at amortised cost using DCF at current day. Instead of creating an amortization table for each loan, is there a vba or formula for this? Also, for this loan portfolio has a premium, so there’s unamortised premium to consider in the valuation as well.

Don PistulkaPost authorVinit,

My links don’t seem to be working, so I will email you.

Don