Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Bravo! Couldn't have said it better!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator bbearren's Avatar
    Join Date
    Dec 2009
    Location
    Polk County, Florida
    Posts
    3,760
    Thanks
    26
    Thanked 424 Times in 338 Posts
    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.
    Create a fresh drive image before making system changes, in case you need to start over!

    "The problem is not the problem. The problem is your attitude about the problem. Savvy?"—Captain Jack Sparrow "When you're troubleshooting, start with the simple and proceed to the complex."—M.O. Johns "Experience is what you get when you're looking for something else."—Sir Thomas Robert Deware.
    Unleash Windows

  5. #5
    New Lounger
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #6
    Star Lounger
    Join Date
    Dec 2010
    Location
    Scotland
    Posts
    76
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    1. In your spreadsheet, label columns for each month.

    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. #8
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,417
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Quote Originally Posted by MartinM View Post
    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.
    BATcher

    Time prevents everything happening all at once...

  9. #9
    New Lounger
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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. #11
    New Lounger
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Fred

    Thanks for the reply & your advice

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It IS a college question !

    Nuff said.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •