Smith Manoeuvre Calculator/Spreadsheet for Excel

By now, most people here know that I’m interested in the Smith Manoeuvre. If you are new to this concept, you can read more about them in my article “The Smith Manoeuvre – A Wealth Strategy“.

From surfing the web researching the Smith Manoeuvre, I discovered a great thread on RedFlagDeals.com that talks exclusively about the strategy. This includes the tax implications, the various mortgages available, and some calculations relating to potential return.

Cannon_Fodder, a regular contributor of RFD has created an Excel spreadsheet that he is willing to share with the world. He was even gracious enough to allow me to share the spreadsheet with you!

You can download Cannon_Fodder’s Excel Smith Manoeuvre spreadsheet here.

Update: Cannon_Fodder has released version 2 of the SM Spreadsheet.

Since the Excel spreadsheet doesn’t come with a manual, I will provide a few notes on HOW to use the program. Note, you are going to have to enable macros when you open the program.

  • Go to the “input” tab which for the most part is the only sheet you’ll need. The other tabs are primarily “Calculation” sheets, however, they can be helpful to see exactly what is going on.
  • Enter all relevant information as it pertains to you. The only portion that I have confusion with was the “Periodic investments” portion. If you choose biweekly payments (26 payments/year), then your periodic investments will be calculated as bi-weekly also. So if you’re accustomed to investing monthly use this formula: Periodic Investment = (Monthly Investment * 12 / 26)
  • Everything else should be self explanatory. I didn’t fool around with the “Cash Flow Damn” option, so I won’t comment on that.
  • Remember that your “Starting LOC” value is the equity in your home. In mathematical terms: (Home Value x 75%) – Mortgage Balance.

Tomorrow, I will go through my personal scenario to see how the Smith Manoeuvre stacks up.

98 Comments

  1. DocB on September 17, 2010 at 2:52 pm

    @FT, I do have macros enabled. The spreadsheet appears to auto-update without me having to hit “calculate”…

    The only thing that doesn’t auto-update is “House Value Growth Rate”… that does not seem to have any functional component in this spreadsheet … clearly I’m missing something.

    Cannon_Fodder is there a tweaked commercial version that I can purchase somewhere?

    Cheers

  2. cannon_fodder on September 17, 2010 at 3:41 pm

    Thanks for the kind words. I created a more robust calculator for commercial purposes. And I took out some features to be able to post a slimmed down version here for free.

    In the commercial version the calculations can be more complex and not ideal when showing a client. You want to put all the numbers in and then hit calculate to see the impact to go from one scenario to another.

    Secondly the house value is not enabled here. It was put in there for the use of readvancing additional equity during the SM every X years.

    I truly believe that this meets the needs of 95% of people. The other 5% would be relevant only if you used this in a financial advisor capacity.

    Please note that the marginal tax rates change and you are allowed to put in your figures as appropriate.

  3. DocB on September 17, 2010 at 5:40 pm

    @CF, thanks for the clarification I was banging my head against my desk for a few hours!

    I find this spreadsheet very useful, I happened upon it by chance after watching a video by David Ingram (below) and then doing a google search for Smith Maneuver in excel.
    http://david-ingram.com/article.php/CanadaMortgageTaxDeduction

    I actually really like the re-advancing additional equity for SM if home value increases, I see some SM mortgages allow for LOC to be increased if home value rises without any legal fees. Is there a way I can activate this feature in your spreadsheet?

    Cheers

  4. cannon_fodder on September 18, 2010 at 8:56 am

    DocB,

    Again, I am glad you find it useful. It certainly helped me a great deal but it was more a labour of love than anything.

    I’m sorry but increasing the readvanceable portion based on a growing home value was a feature created for a client in the commercial version. There is no way to activate it.

    You could manually adjust it by running a scenario, say, for five years. Copying the end numbers down and then using them to seed the new scenario. It wouldn’t be easy hence why I was contracted to make it automatic.

    Cheers

  5. James on October 9, 2010 at 5:11 am

    cannon, your calculator is magnificent, I really appreciate the fact that you shared this with us.

    I have a query in regards to the Annual Prepayment box. Using my parameters, leaving the box as $0 results in a Total cash flow of $386,000. Populating $5,000 in the AP box results in a Total cash flow of $383,000. Since the mortgage will be retired in 8 – 9 years the $40k in annual payments have only saved me $3,000 in payments on the mortgage. Is this correct?

    I see where the effect is noticable in the Investment portfolio… I guess I just thought that dropping $5k each year on the mortgage would provide a lot more “bang”.

  6. cannon_fodder on October 10, 2010 at 1:20 am

    James,

    Without having all of your figures that sounds about right. You can almost think of it like investing your prepayment into something that provides a return equivalent to your mortgage rate. How much would that increase your investments over the period?

    If you had a longer period to pay it off or a higher interest rate the effects would be more significant.

  7. Lucius on February 28, 2011 at 4:49 pm

    Thanks for the info CF. I’m a noob when it comes to money management. I started looking into it more when I was thinking about doing a manulife one LOC on my primary home (we have a rental as well) and came across this site (when searching for SM).
    We have an appointment with our first financial advisor this week. I just didn’t think it was needed but boy can I see the error of my ways. Cheers, Lucius

  8. 200above on April 2, 2011 at 6:57 pm

    I have Version 2.0 and all I seem to get is #VALUE in all the calculation boxes. I have macros enabled, but still, only #VALUE in the yellow boxes?

    Any ideas what I’m doing wrong?

    * Disregard, I played around with it some more :) *

  9. margaret stec on October 29, 2011 at 3:05 am

    Thank you for the calculator.However I can’t safe it to use it as much as I want to. It requires to disable” macros “from computer security.At least that how I understand it.

  10. cannon_fodder on October 29, 2011 at 4:01 pm

    Margaret,

    In order to provide the functionality I required, I employed macros. You can purchase Fraser Smiths calculator for $30 if I remember correctly.

  11. gnatwest on December 16, 2011 at 1:11 pm

    Im stuck with #value in all of the yelllow boxes and nothing showing on the chart.

    Any help there would be appreciated.

  12. Ed Rempel on January 28, 2012 at 1:45 pm

    Hi gnatwest,

    Try enabling macros.

    Ed

  13. M&M on February 2, 2012 at 1:02 am

    So, I’m thinking of giving this a SM a try. I’m trying to get this calculator to work and am not sure I am looking at things correctly.

    Here is the scenario: Our credit is good, we are debt free, have no children, and make good livings. Won’t include any retirement assumptions, as this is something we’d be looking at separately from retirement. My soon to be wife and I currently make about $250,000/yr and live in AB. We are looking to purchase a home for, give or take, $750,000. We plan to put 20% down, or $150,000, leaving us with a mortgage of, give or take, $600,000. I will use the following conservative assumptions: mortgage amortized over 25 years, mortgage interest of 5%, HELOC of $100,000 (2/3 of the equity) at 5%, a flat mortgage payment of $2000 biweekly, and an expected return of 4% on investment.

    If I want to do the calculations, do I put $100,000 as my starting HELOC and also $100,000 as the non-registered asset? I am assuming I do. Further, if adding a periodic investment, am I to assume that investment is put onto the principal of the mortgage, then borrowed against?

    Been a reader and subscriber for a while, enjoying it, and this is my first post.

  14. PuddleJumper on February 19, 2012 at 8:03 pm

    I’m not sure why but almost all of my cash flow values come back negitive? My total cash flow after 25 yrs a larger negative number than my mortgage was.

  15. PuddleJumper on February 19, 2012 at 8:09 pm

    Yeah never mind just realized its just my payments toward investing and mortgage….

  16. Noah on March 8, 2012 at 2:55 am

    When i draw money out of my LOC to buy a T series fund, does it need to be unregistered in order to collect the dividends and use them to pay down my mortgage without being taxable?
    Or can I buy it as an RRSP and still collect the dividends tax free.

    If I use the dividends to pay down the mortgage, does that make the loan on the LOC non tax deductible by the amount of the dividend? (with no ROC)

    What I think I would like to do is use the dividends to pay down the principle on my mortgage on top of my weekly payments,
    draw out the principle paid on the mortgage from all payments (original mortgage payment, dividend payment, tax returns) towards the mortgage and draw it from the LOC to re-invest into a fund,
    draw from the LOC to pay the LOC interest
    and continue to repeat untill the whole mortgage loan is on the LOC.

    To cash dam the process, do I just need to create an account for the dividends to be deposited into before they are put on the mortgage?
    Or do I need and account to deposit into from the LOC before I buy more funds?

    If I do this, will the total LOC interest payment be able to be used for my tax refund during paying down the mortgage and after the mortgage is finished being paid?

    If so, I assume I could then continue to draw from the LOC to buy more investments, and continue to use the dividends now pay down the LOC until my fund is the size I am happy with, correct?

    Then when I am done growing my fund, I can stop drawing to increase the fund, but still use the dividends to pay down the LOC until it is gone, correct?

    I hope I made some sense there, I’m pretty wet behind the ears still.

  17. Ed Rempel on March 9, 2012 at 1:25 am

    Hi Noah,

    Wow, that is a lot of questions.

    – Yes, the investments must be non-registered. Interest for investments in an RRSP are not deductible and any cash withdrawal from an RRSP is fully taxed as an RRSP withdrawal.
    – Payments you receive from a T series fund are ROC, not dividends.
    – Yes, if you take the payments and don’t pay all of them onto the investment credit line, then the each payment reduces the amount of the credit line that is deductible.
    – No, cash dam is a completely different process involving borrowing to pay the costs of a business or rental property.
    – No, if you are using a T series fund, then you can’t use all the interest on the credit line as a tax deduction. You need to prorate it based on the amount of payments you have received. For the credit line to remain deductible, any amounts you take from the investments must be taxable income, such as a dividends or capital gains.

    Noah, this type of strategy may not be right for you. You seem to be focusing on the cash you can receive from the investments, but you need to understand that borrowing to invest is a riskier strategy. The investments will go up and down, and you need to be able to remain focused on your strategy long term.

    It is more risky if you are taking cash payments from your investments, since that makes it more likely that the investments might not recover after a decline.

    Before you consider a strategy like this, make sure you understand the risk.

    Ed

  18. Noah on March 9, 2012 at 1:47 am

    sounds like i’m going to do this through an investor
    I’m guessing you’re not in BC based on the 905 area code

    thanks for the quick feedback.

  19. Noah on March 9, 2012 at 3:26 am

    one more question, OK a couple more:

    If I invest in a non registered fund that only pays dividends and no ROC, the dividends are taxable right? So if I am paying tax on my income from my fund, I could then use that new income to pay down my mortgage or make other purchases without it affecting the tax deduct-ability of the investment loan right?

    If the fund did pay ROC, then I could use the income from it to pay off the investment loan and maintain the tax deduct-ability of the loan right?
    Then would I be able to use the money I get back on my tax return from the interest paid on the investment loan as a lump sum into the mortgage and still have the investment loan remain tax deductable?

    If I withdraw from my HELOC into a Clearing Account, then use money in the CA to pay any remaining interest due on the HELOC and the remainder of the money in the CA into the fund, does the portion originally from the HELOC that when into the investment fund remain tax deductable? Does the money used to pay the remaining interest on the HELOC become tax deductable as well?

    thank you

  20. newmanity on March 24, 2012 at 12:44 am

    Thanks for the excellent calculator, cannon_fodder! It has helped me tremendously in understanding the calculations involved. I did have one question, though: how are you calculating the dividends when re-investing? When I calculate this myself I get the same values initially but they eventually decrease in comparison to yours (whatever the scenario) with the eventual effect of the money available to invest decreasing rather than increasing over time…it’s driving me nuts, and I’m assuming the error is mine!

  21. Ed Rempel on March 24, 2012 at 3:17 pm

    Hi Newmanity,

    Spreadsheets can be tricky, since a small mistake in logic tends to get magnified.

    Without seeing a formula, I can’t tell you what is wrong, but I can guess. Do you have a mortgage amortization calculation in your spreadsheet? The tricky part of the formula is that:

    1. each year you can borrow more because you are paying down more principal as your mortgage balance declines.
    2. each year you need to pay more interest on the credit line for money you previously borrowed.

    These 2 opposing forces tend to be roughly equal. If you are getting less to invest each year, then my first guess is that your mortgage amortization calculation does not reflect the increasing principal portion being paid down.

    You should look closely at your formula for the end of the 1st year and verify the figure with a manual calculation.

    I do have a question, though. You already have Cannon’s calculator. Why are you trying to recreate it?

    We analyzed it indepth and verified the calculations independently and found the calculator to be very accurate.

    Ed

  22. Ed Rempel on March 24, 2012 at 3:33 pm

    Hi Noah,

    The answer to the 3 questions in your “one more question” is yes: :)

    1. If you take taxable dividends from an investment, you can use them any way you want and the interest remains deductible.

    2. If you receive ROC and pay it onto the credit line, the remaining credit line is deductible, which means you can pay your refund onto your mortgage.

    3. If you take money from your credit line to put into a dedicated chequing account, you can use it both to pay the interest on the credit line and to invest more. This is called “capitalizing” the interest – when the credit line pays its own interest. The interest on the interest is also deductible.

    It sounds like you are trying to figure out the best strategy. Here are my comments on the 3 strategies implied by your 3 questions:

    1. This is a dividend strategy that sacrifices growth to reduce your mortgage. You lose growth for 2 reasons: you ignore investments that don’t pay dividends and you pay tax on the dividends each year. We call this the “Smith Manoeuvre with Dividends”.

    2. I call this strategy the “race to zero”. You take a fund with the highest possible ROC payment to pay down your credit line. Which will hit zero first? If you stick with an equity fund, it should be able to maintain itself, but if you have a balanced fund paying out 8-12%, expect a decline. This is a form of the “Smith/Snyder” strategy.

    3. This strategy is actually the Smith Manoeuvre. It has much higher long term growth potential than the previous 2. The expected benefit of the simplest form of the Smith Manoeuvre over 25 years it double your current mortgage. It rises exponentially because the secret is that your investments remain untouched and compound exponentially over time. The first 2 strategies are more about reducing debt than building wealth.

    Ed

  23. newmanity on March 24, 2012 at 5:33 pm

    Thanks, Ed.
    I’m an engineer and so I like to see how things work ;) I’m sure the spreadsheet is right. I get the more principal being paid down and the more LOC interest to pay. All my numbers are identical to cannon_fodder’s except for the dividend column which is where (and only where) a discrepancy occurs. When re-investing dividends this adds to the $ to invest, and I’m calculating the dividends by multiplying each biweekly amount to invest by the dividend return % divided by 26. This must be too simplistic because after 5 months the numbers start to diverge…

  24. Ed Rempel on March 24, 2012 at 7:19 pm

    Hi Newmanity,

    Yes, I know you engineer types. One of our advisors is also an engineer.

    I went through what you are doing myself, just to prove to myself that it is accurate, since we give it to clients. We have a custom version.

    Your dividend calculation sounds like it should be close. Are you sure it is the dividend calculation, not the investment or mortgage balance after you apply the dividend?

    Are you paying the dividend onto the mortgage and then reinvesting? Are you using the dividend amount before or after tax?

    The calculator applies the dividends to the mortgage, reborrows the amount from the credit line to invest, and then pays the tax on the dividend.

    Ed

  25. cannon_fodder on March 25, 2012 at 12:21 am

    Just saw a flurry of email alerts about this thread. I’m away right now and not sure what the discrepancy looks like.

    My initial guess is that the spreadsheet I uploaded still uses marginal tax rates from 2009 or so. If you’ve updated your own to 2012 (I believe I left those cells unprotected in that worksheet tab so you may be able to modify mine) you might find your answers align.

    And if I didn’t like to see how things work for myself, then this project of mine would never have been started. I applaud anyone who is willing to make the effort to create their own model.

  26. newmanity on March 25, 2012 at 5:21 pm

    Thanks again for the feedback guys!
    CF: I’m using your 2009 numbers for now, so the inconsistency isn’t from different marginal tax rates.
    Ed: I’m testing the sheet with only dividend re-investing right now (not applied to the mortgage first) so I’m simply adding the dividend to the “$ to invest”. The error is definitely in the dividend calculation because all my other column values are identical to CF’s. My $ to invest values become different than CFs only because of the dividend contribution part being off. The first few numbers in my scenario for “Cash to Mtg – Interest Costs” are (like CF’s): [400.281; 399.976; 399.674; 399.370]. My 2% dividends are [0.000; 0.308; 0.616; 0.924], leading to “$ to invest” values of [400.281; 400.284; 400.289; 400.294]. The last dividend value for CF is 0.93 instead of my 0.92, and this is my discrepancy. I get my 0.308 by 400.281*0.02/26…but this leads to diverging numbers eventually….and this must be where my mistake lies.

  27. Noah on March 26, 2012 at 12:01 am

    Thank you very much Ed!
    That helps me alot, too many people are telling me different things and what you said follows with what I thought I could do.

    once again, thank you.

  28. Eric P on April 3, 2012 at 8:40 pm

    Hey Cannon Fodder, can I contact you regarding the spreadsheet? I have some questions regarding the possibility of licensing it. How might one go contacting you?

  29. cannon_fodder on April 7, 2012 at 12:29 pm

    Eric,

    please contact Frustrated through this site and he will forward your contact into to me.

    Thank you.

  30. cannon_fodder on April 7, 2012 at 12:32 pm

    That’s FRUGAL TRADER, haha! Silly autocorrect.

  31. Prime on August 4, 2012 at 2:11 am

    I must be missing something, the spreadsheet(V2_00) doesn’t work for me. In the results box all cells except Mortgage Retired and Amortization Reduction(which doesn’t look right as well) say #VALUE. Can you guys help me out ?

    All the inputs except Annual Prepayment and Cash Flow Damn are filled

  32. cannon_fodder on August 5, 2012 at 10:57 pm

    prime,

    did you enable excel macros?

  33. Prime on August 6, 2012 at 8:56 pm

    Hi Cannon,

    Yes, Macros is on.

  34. cannon_fodder on August 7, 2012 at 10:01 am

    Prime,

    What version of Excel are you using? I haven’t tried it with the free versions that PC’s come with these days and I suspect they don’t support some of the functions I used. There are a lot of features stripped out of the Office Starter version of Excel.

  35. Roger N on January 6, 2013 at 9:17 pm

    For those plagued with #VALUE errors, note that the spreadsheet requires that your regional date format preference (located in a Windows control panel) is set to the USA convention (month/day/year). The spreadsheet fails when using other conventions, such as yyyy-mm-dd.

  36. #BP on March 22, 2013 at 12:29 pm

    Cannon

    I am having a lot of difficulty with the calculator as well. Anyway you can send a cheat sheet of some sort for us non excel brainiacs out there

  37. Jarvis N on December 12, 2013 at 12:34 am

    Overall I think most of the Calculator is error free, however I did find an error, and it was actually identified by Cannon_Fodder near the top of the comments.

    When I have an investment that has 0% investment increase and a 0% growth rate, but has dividends of 9% I should be getting an investment value that grows faster than the LOC (this is because a LOC at 4% annually is cheaper than Dividends at 9% quarterly –> Dividends quarterly = 2.25% LOC quarterly costs 1% Therefore 2.25% > 1%)

    I went through the model and I identified that the problem lies within the Investment LOC or the Investment column in the Calculations Worksheet. The Investment LOC is increasing by the total mortgage payments whereas the Investment is only increasing at the PRINCIPAL + DIVIDEND – LOC INTEREST COST).

    I can’t quite pin point it, but I think that the Investment account should grow at the new available LOC and not at the previous investment level. It’s very difficult to try to nail down the error, however, because I don’t understand the difference between the Investment and Investment LOC columns. They really should be one in the same.

  38. Matt on April 3, 2014 at 1:02 pm

    Good morning!

    Hoping this is still being monitored, but I see the last comments are over a year ago.

    One quick question – what is the purpose of entering a value for “periodic investments”? Wouldn’t the override payment box account for any additional principal being paid down and thereby moved over for investment via the HELOC? Conversely, if its meant as an outside-funds periodic investment, wouldn’t that create problems with proving that all of the mortgage interest was deductible, since we’ve now commingled funds?

    Thanks!
    ~M

  39. cannon_fodder on April 3, 2014 at 4:49 pm

    Hi Matt,

    That’s a great question… the idea behind the “periodic investments” was indeed for the latter scenario. It gives someone the option of also investing outside of the SM. That doesn’t mean of course you have to put them in the same account as your SM account (thus bypassing the problem of commingled funds) although it does not allow for the flexibility of having its own growth rate.

  40. Matt on April 4, 2014 at 1:25 pm

    Hi cannon-fodder,

    Understood. Makes sense (from an investment perspective) to see how additional non-reg dollars could pump it up even further.

    Thanks for a great calculator. This takes a lot of the guessing and manual labour out of a complicated strategy.

    Now…to wait for my mortgage to reach the end of this term…and waiting is the hardest part…

    Cheers,
    ~M

  41. K on April 14, 2014 at 5:17 pm

    Hi cannon-fodder,

    Great spreadsheet, I’ve been using it to compare various smith maneuver options.

    I’m interested to pay for some RM scenario calculations. Is that a service you still offer?

    Thanks,

    K

  42. cannon_fodder on April 20, 2014 at 8:45 pm

    Hi K,

    Why don’t you post some scenarios here because if we can accomplish the calculations with a bit of guidance and no customization, then everyone can benefit and there would be no cost.

    I only charged for customized versions of the SM calculator.

  43. cannon_fodder on April 20, 2014 at 8:46 pm

    Hi K,

    Why don’t you post some scenarios here because if we can accomplish the calculations with a bit of guidance and no customization, then everyone can benefit and there would be no cost.

    I only charged for customized versions of the SM calculator.

    Thank you,
    c_f

  44. K on April 21, 2014 at 11:41 pm

    Great Idea, thanks Cannon.

    Could you run the Rempel Maximum with the inputs below?

    I would be very interested to see what happens with different dividend treatment: reinvest vs. pay down, re-borrow and invest.

    House Value: 400,000
    Mortgage: 200,000
    Amortization: 25 years
    Mortgage interest rate: 5%
    Monthly payments: 1169.18

    HELOC Balance: 50,000
    HELOC interest rate: 6%

    Investments: 50,000
    Dividend Yield: 3.5 %
    Growth Rate: 3.5%

    -Marginal dividend tax rate: 6.46%
    -Apply tax refunds, re-borrow and invest.

    Thanks,

    K

  45. Brad on August 11, 2014 at 4:22 pm

    Also having difficulty getting macro to run. While some fields update automatically when data is entered, the Calculate button does not seem to work. In addition, it does not seem toggle when I click (does not appear to go down then up).

    I notice too that if I try to change the checkboxes associated with Steps 1 – 4, it selects the field control, rather than checking on or off. Almost as if the form is not protected and therefore operational.

    Have tried with Macros wide open, and with ActiveX controls also wide open, on both Mac (version 2011), and on Windows (Office 2007).

    Any help greatly appreciated.

  46. Brad on August 11, 2014 at 5:59 pm

    Ok, I’ve answered my own question. Downloading on a Mac (or perhaps opening first on a Mac) screws things up with the file. Apparently the ActiveX controls do not work on a Mac.

    So only download to on PC, and run on PC.

  47. Michael Wollman on August 12, 2014 at 4:35 pm

    Thank you Cannon, I created my own spreadsheet before I found out about this site. I actually new to this and I found that your spreadsheet is an excellent tool.

  48. Mat on August 16, 2018 at 3:49 pm

    Am I correct in my thinking this calculator doesn’t include capitalizing the LOC interest? If not, how does it get paid if you’re putting all your cash flow towards paying down the mortgage? If it does include capitalizing interest wouldn’t the total value owing on the LOC be much more than the original mortgage amount?
    Thanks Mat

Leave a Comment





This site uses Akismet to reduce spam. Learn how your comment data is processed.