Bank loan assessor

Use this Excel spreadsheet to compare two bank loans, or to calculate the actual rate of interest being charged on a loan.

Features

  1. Converts all fees into a cost of money - the interest rate - making loans with different fees comparable
  2. Copes with bullet payments (where an element is repaid as a lump sum at the end)
  3. Exposes the flat rate sham
  4. Shows the actual (nominal) rate of interest the bank is charging, along with the corresponding APR
  5. Copes with monthly payments for terms up to 30 years
  6. Shows the amount of debt outstanding at any point during the loan
  7. Quick calculator for simple cases where you know the repayment and want to know the rate

Calculator inputs

How to use it
It's fairly easy.  Just enter the figures the bank has quoted you into the white space.

The bullet payment is where you have the option to  pay a lump sum at the end, which could be all of the principal (the sum advanced) making it interest only, or just part.  Set the bullet high enough, so that the monthly repayments are nil, and you have created a "Zero", where all the capital and interest are rolled into a single payment at the end.

There is a drawback. You can't enter the monthly payment the bank quotes you, because there are too many other variables to take into account when calculating the actual rate being charged.  After you have entered all the other figures, adjust the interest rate until you get the correct monthly repayment. This might be a bit fiddly.   Alternatively use Solver.

To use solver to find the interest rate for the loan offer, use the drop down menu Alt | Tools | Solver.   If it isn't there, you need to include it from the add-ins  -   go to Tools | Add-ins | Solver.  This may require the installation disk if the add-in was not included originally.


Comments
A)    Arrangement fees are treated as a reduction in the amount of the loan advanced.  If you are lent £100,000 and charged a £5,000 arrangement fee, then really you have only been lent £95,000.  The bank, however, will be charging you interest on £100,000 and quoting an apparently lower APR.  A small arrangement fee can make a big difference to the APR and, crucially, the total amount of interest paid.

B)    This is for fixed, not variable rate loans.  We don't know what the future variable rate will be.  However, you can use this to experiment with different rates.

C)     If the loans are for different amounts or terms, it will query whether you are comparing apples with apples, but leaves the answer to your judgement.

Comparison output

Login to post comments