1. ## Comparing mortgage loans

I have a business mortgage of \$ 80M which was taken out several years ago and the balance owing currectly is \$ 25.5M at an interest rate of 5.5. I require \$ 100M for expansion purposes which I can finance as follows:
1) \$54.5M can be advanced on the original loan
2) I have cash of \$ 15.5 which I can deposit into the current mortgage and receive interest at 5.5 % until the funds are required, but will be charged a deposit penalty of 0.1% per month
3) I can borrow \$ 30 from the current bank at 6.0%. A opposition bank is prepared to lend me \$ 30M at 5.85%
4) I can borrow the full \$ 100 from an opposition bank at 5.75%. However If I cancel the existing morgage with the current bank, there is a penalty for cancellation of the morgage of 1.2%

I would like to set up a spreadsheet to do a comparison to determine which is the better deal.

Your assistance in doing this is most appreciated

2. You have not provided nearly enough information to answer your question (sounds like a college question to me, but what do I know !). Just for instance, you say "receive interest at 5.5 % until the funds are required" but you don't say when that will be.

However, assuming that this is a real situation, may I observe that you should not make business decisions using the financial understanding of an Excel expert. It is you, or your accountant, who has to understand how the finances work, then - if you need it - seek help in representing it in Excel.

Usually, once you have worked out the payments and costs in each alternative, the Excel work is simple and that is the time to seek help if you still need it.

3. Martin,

Bravo! Couldn't have said it better!

4. You've left out way too many parameters. Go back and re-read that homework assignment and see what you're missing in the OP.

5. Hi Martin

Thanks for the reply. The funds (15.5M will be deposited into the mortgage account for a two month period) The interest earned will be the same as the interest paid i.e 5.5%, except there will be a deposit penalty of .1% for each month i.e two months 0.1% x 2 = 0.2%

The penalty canlcelation is on the original mortage, which is 1.2% x \$ 80M

I am not seeking any advice, but rather how to set this example up on Excel

6. If you search templates in microsoft excel online there are many mortgage templates ready made. I'm betting there may be one ready made for the job.

Then, or each of the possible scenarios:

2. Write down the net of payments, credits and charges month by month and put them into a row. Or you can use several rows and have Excel wok out the net.
3. In the row below, put in a running total.
4. Do the same for the next scenario.
5. Etc

Then compare the running totals at the end.

PS You never said if this is "real life" or a college question ?

8. Originally Posted by MartinM
You never said if this is "real life" or a college question ?
I think in real life if someone took out a \$80M loan then there's a fair likelihood that they had someone on the staff who knew a little Excel.

9. Hi Martin

Thanks for the reply and the pointers.

The loan is a "real life" situation where I am comparing two loans where the one loan theere is a penalty is one deposits surplus funds or pays the loan early and tother loan there no such penalties, but the second loan thev rate is higher

10. In addition to the advice given by many of the replies, another thing I have found that is hard for others to advice you on is what is meant by "the better deal."

For example, your #2 choice mentions depositing money. That cuts down liquidity. Another dimension of "better" might be paying the least in interest or the least in taxes.

It might turn out that after evaluating the different scenarios that you find one to be better than another for one reason but worse for another reason. That suggests you might have different objectives, all of which should be evaluated by your spreadsheet. Only you can then decide which is the better one for you.

Fred

11. Hi Fred