Using excel to estimate borrowing capacity

L

laragh

Registered User
Messages
101
Hello all!

I am trying to get excel to work out the following...

Given the following data-

  • Interest rate
  • Loan term
  • Amount of free cash available to make loan repayments during the year
Is there a way I can get excel to calculate what the maximum loan is that the free cash available will service??

Been tearing my hair out on this one for a while now...
 
Thanks Clubman! I know that's a potential way of doing it but I am trying to set up a spreadsheet for a collegue that will do this automatically. Based on the amount of cash available to meet loan repayments (which is calculated using other criteria) then based on user entered figures for loan rate & term then excel will return the maximum principle figure..

maybe this is not possible in excel without very fancy programming skills....
 
I don't really understand. Surely the amount of mortgage that can be comfortably afforded will be dictated by the minimum monthly repayment (from income and/or savings) that can be stumped up with some wiggle room to allow for potential rate increases? What's the point in calculating the maximum loan that can ba afforded dynamically?
 
Try using the function PV
Go to help, type in financial functions, scroll down until you get to PV and follow examples

You can input the details within the brackets into 3 cells within excel to calculate the PV
 
Many thanks asdfg- that seems to give me the result I need.

Clubman, in answer to your question, I fully agree that this way of calculating the maximum amount that a business can borrow is not good practice. This calculation is to give a general indication of what size of loan could be serviced by the amount of free cash generated by a business (a farm business in this case).

My intention would be to put together a full repayment schedule in excel for any loan so it is clear what the cash requirement per month would be for the loan in question.
 
Sarah W said:
Don't forget that lenders stress test mortgage repayments at 2% over current rates......
Click to expand...
Has that figure been the general rule of thumb over the years or has it changed depending on the interest rate environment (e.g. was it higher or lower when prevailing rates were higher?). I presume it's not recommended or stipulated by the Central Bank? Just curious...
 
Lenders have stress tested mortgage repayments (at between 1-2% over base - it hasn't really changed as rates, until recently, have been stable at 2%) ever since they moved away from the income multiple to the net disposable income method of calculating affordabilty. It's not a Central Bank requirement AFAIK.

Sarah

www.rea.ie
 
I've developed a piece of software which lets you enter any 3 of the following four figures:
  • Interest Rate
  • Mortgage Amount
  • Term of Mortgage
  • Repayment Amount
It will then calculate the forth figure. So, for example, if you say "I've got 800 euro a month to spend and I want a 30 year mortgage and interest rates are 3.5%", you can insert these 3 figures and the software will work out how much you can theoretically borrow.

It will also calculate the effect overpayments on your mortgage will have as well as increases in interest rates.

It can be downloaded from

[broken link removed]

Let me know what you think of it...
 
Whilst there's no harm devising a computer programme to calculate how much you can borrow this may not always come to the same figure as a lending institution (in both directions) so it would do no harm to approach lenders or brokers simultaneously to confirm that your calculations are in harmony with their criteria.

Sarah

www.rea.ie
 
laragh said:
Hello all!

I am trying to get excel to work out the following...

Given the following data-
  • Interest rate
  • Loan term
  • Amount of free cash available to make loan repayments during the year
Is there a way I can get excel to calculate what the maximum loan is that the free cash available will service??

Been tearing my hair out on this one for a while now...
Click to expand...

There was a good link on a post, to an excel sheet that does something like what you want. Can nt find the post, but I downloaded the file. If you want I can e mail it to you, just send me your e mail address on the private messages.
 
It was me that originally posted that Badger. After thinking about it properly I realised that although that it is by far the best one freely available on the net it wasn't informative enough or flexible enough to really analyse a loan. I wanted to see exactly how much I could save in interest by changing payments every month if neccessary yet all the calculators out there let you fill in one extra payment slot across the term of the loan.
To cut a long story short I wrote an excel template myself which I am very happy with. You can
Calculate your monthly payments on any loan from 1 - 40 years in length with any interest rate you wish.
See how much interest you will pay over the course of your loan.
See on a month by month basis what portion of your payment is interest and principal.
Check the effect of interest rate changes on your mortgage payment / interest paid over the term.
See what effect additional monthly and one off payments have on your loan including how much interest you could save and how many years less it will take you to pay off your loan. Best of all it's in € :D

If anyone wants it completely foc drop me a mail at patforde@iol.ie or pm me your email here.

( Sorry if that looks like an advert mods - it genuinely isn't )
 
You must log in or register to reply here.
Back
Top