[FrugalTrader]

Our very own Cannon Fodder has generously offered to share his Excel based calculators to the CMF community. You can download them by clicking the links below.

• Constant After Tax Income
• Fixed Rate Mortgage vs Variable
• Loan Comparison Calculator
• RESP Calculator
• RRSP Meltdown Calculator
• Shopper Drug Mart Optimum Points Calculator
• Borrowing to Invest - TFSA vs RRSP vs Non-Reg vs Mortgage Paydown
• Leveraged Investing - Non-reg vs. RRSP vs TFSA

 

[cannon_fodder]

Here is a brief summary of what each spreadsheet attempts to demonstrate:

Constant After Tax Income - Perhaps you’re thinking of the future and how much income you will need in retirement. Wouldn’t it be nice to figure out how much your nest egg will provide for you? This calculator takes into account capital gains taxes (for non-registered portfolios), marginal tax rate and even the inflation rate to give you a constant, inflation-protected stream for as many years as you input.

If, on the other hand, you have an idea how much after tax income you will need in the future and are looking to figure out the size of the nest egg you’ll need to accumulate, this calculator will figure that out, too.


Fixed Rate Mortgage vs Variable - Are you currently in a fixed rate mortgage and wondering if converting to a variable rate mortgage might be better for you? This calculator takes into account any penalty for breaking the mortgage early and allows you to adjust the interest rates during the term up to 5 times. This represents the fluctuating nature of the Bank of Canada prime rate which will impact your variable mortgage rate.


Loan Comparison Calculator - There are different types of loans out there: non-deductible compounding monthly (e.g. car loan), deductible compounding monthly (e.g. borrowing from a HELOC to invest), non-deductible compounding semi-annually (e.g. Canadian mortgage) and deductible compounding semi-annually (don’t know where you would find that, but it’s included anyway). Some loans you pay down the principal and interest (like a car loan or mortgage) while others you may choose only to pay the interest (e.g. a HELOC used to fund investments).

This calculator was born out of my musings of the Smith Manoeuvre and the resultant deductible HELOC that is left at the end. I wondered how that would compare to a traditional, non-deductible loan in today’s dollars vs. a deductible loan in future dollars. That is why you will also see inflation factored in.

In addition, there has been talk about Manulife’s M1 product. I’ve come up with a calculator that allows you to compare mortgages with M1 including their monthly fee and I’ve been about as generous as I can with the M1. For the most part, the M1 product was offered at a higher rate than a typical person could easily get at any large FI in Canada. The recent economic crisis (late 2008) has changed that but this could be temporary.


RESP Calculator - We all know about the magic of compounding so if you can get more money invested sooner, rather than later, it has more time to grow and grow. But, with RESP’s the government has limited the amount they will kick in (via the CESG) not only over the lifetime of an RESP but also per year. We can’t forget that many people don’t have thousands of dollars lying around to jump start an RESP and it is clear to see that most people contribute a modest amount each year.

For those of you that want to see what would happen if you could make 1 big contribution up front and whether or not your child(ren) could have more money for post secondary education, this calculator may be able to help.

This calculator will allow for future proofing if the government extends the maximum lifetime contribution, the CESG grant or even the annual contribution limits after which no more CESG money is kicked in.


RRSP Meltdown Calculator - If you’ve ever heard claims about how you can withdraw money from your RRSP tax free then it likely concerns borrowing to invest.

The idea is that you borrow an amount of money to invest that meets the CRA’s deductibility criteria. You then pay the interest on the investment loan with money that you withdraw from your RRSP. Since the RRSP income and the investment loan deductions both cancel each other out whatever your marginal tax rate, you end up funding your investment loan with your RRSP.

There are some schools of thought that this could be worthwhile to look into before you retire, especially if you have a lot of money tied up in your RRSP but nothing in TFSA’s or non-registered accounts. The idea being that when you convert your RRSP to a RRIF you will be forced to withdraw at the government’s pace and the income will be taxed most unfavourably. If, however, you had more money in TFSA’s and non-registered accounts, you not only would achieve better tax efficiency, you also have more control of where your income is sourced. Doing this before you retire gives you more time to get your house in order.


Shoppers Drug Mart Optimum Points Calculator - If you are a fan, or a fanatic, of Shoppers Drug Mart’s Optimum program, then this calculator may be of help deciding whether or not their 20x points days, or 10x points for certain product purchases justifies paying a little bit more than at another local store.

It takes into account whether you purchase a Gift Card first to use for your actual purchases, Refer-a-Friend events where you can get bonus points by referring other people who shop, promotional points on items, and bonus multiplier days (such as the lucrative 20x points days).

It also allows you to input how many points you currently have and then see your projected grand total which it translates into actual dollars for regular days and bonus redemption days (special days usually held soon after 20x points days where 40,000 points and 75,000 points are worth significantly more.


Borrowing to Invest - TFSA vs RRSP vs Non-Reg vs Mortgage Paydown - If you are contemplating borrowing to invest, but want to see whether the tax deductibility of a loan to invest in a non-registered account outweighs the tax free growth and withdrawal of a TFSA account or the tax refund of a big RRSP contribution and the subsequent application of the tax refund to pay down your mortgage, then this might help.


Leveraged Investing - Non-reg vs RRSP vs TFSA - If you are contemplating borrowing to invest, but want to see whether the tax deductibility of a loan to invest in a non-registered account outweighs the tax refund of an RRSP account or the tax free growth and withdrawal of a TFSA account, then this might help.

 

[high octane]

Thanks!

The last 2 will be very handy to me but the Borrowing to Invest one doesn't seem to fully work. I don't get a solution for the nonregistered option is it just me?

 

[cannon_fodder]

Thanks!

The last 2 will be very handy to me but the Borrowing to Invest one doesn't seem to fully work. I don't get a solution for the nonregistered option is it just me?

You're better than me... I can't even get the thing to open! I don't understand why it is in the form it is rather than the xls file I have.

I will send it to CMF again and perhaps that will fix the issue.

 

[FrugalTrader]

Fixed!

 

[Sampson]

Thanks!

 

[cannon_fodder]

I should note that if you don't have Excel, some of these calculators can be imported into Google Docs and work (although they aesthetically need to be adjusted and features like drop down lists don't work). I don't know about Open Source or Excel-compatible spreadsheet programs - perhaps they provide a better match.

Some of these spreadsheets use macros which will not allow them to work with Google docs.

I've tested the following to show that they work with Google docs:

Canadian Dividend Stocks In Non-Reg vs. TFSA vs. RRSP (not yet up on this site)
Constant After Tax Income
Fixed Rate Mortgage vs Variable
RESP Calculator
RRSP Meltdown Calculator
Shoppers Drug Mart Optimum Points Calculator
Leveraged Investing Non-Reg vs RRSP vs TFSA

Borrowing to Invest - TFSA vs RRSP vs Non-Reg vs Mortgage Paydown makes use of some VBA code for the Non-Registered calculation, so you could say 2/3's of it works...

 

[steve41]

Remember.... these appear to be discrete spreadsheets, so use them understanding that our finances are integrated. Looking at a loan, or your RESP, or TFSA as a free standing entity (in a vacuum) can be problematic. Ideally, the optimum model should incorporate all financial entities as one spreadsheet.

Also.... income tax is a major constituent of our planning universe. Many modellers approximate the effect of income tax as a simple marginal or average tax rate, whereas in reality, the marginal tax rate is a meaningless concept. Income tax is a complex computation with multiple tax rates and thresholds (indexed to inflation), credits (age/dividend/interest deductibility), OAS & GIS clawbacks, not to forget each province has it's own independent progressive tax algorithm.

Questions such as "TFSA or RRSP?", "pay down loan or pay into RRSP?", "borrow for investment?", "Dividend/capgains implications?", "do I sell the family cottage in 10 years or pass it on to the estate?"..... All of these questions are intimately linked to income tax, and if tax is not incorporated to significant detail and instead is approximated with a single average tax rate or MTR... you will get erroneous results.

Tax, and the complex way it interacts with the various forms of capital (reg/nonreg/tfsa/capgains/dividends) as they come in and out of play over time should be a major part of a comprehensive financial plan, IMHO.

 

[cannon_fodder]

Remember.... these appear to be discrete spreadsheets, so use them understanding that our finances are integrated. Looking at a loan, or your RESP, or TFSA as a free standing entity (in a vacuum) can be problematic. Ideally, the optimum model should incorporate all financial entities as one spreadsheet.

Also.... income tax is a major constituent of our planning universe. Many modellers approximate the effect of income tax as a simple marginal or average tax rate, whereas in reality, the marginal tax rate is a meaningless concept. Income tax is a complex computation with multiple tax rates and thresholds (indexed to inflation), credits (age/dividend/interest deductibility), OAS & GIS clawbacks, not to forget each province has it's own independent progressive tax algorithm.

Questions such as "TFSA or RRSP?", "pay down loan or pay into RRSP?", "borrow for investment?", "Dividend/capgains implications?", "do I sell the family cottage in 10 years or pass it on to the estate?"..... All of these questions are intimately linked to income tax, and if tax is not incorporated to significant detail and instead is approximated with a single average tax rate or MTR... you will get erroneous results.

Tax, and the complex way it interacts with the various forms of capital (reg/nonreg/tfsa/capgains/dividends) as they come in and out of play over time should be a major part of a comprehensive financial plan, IMHO.

steve41,

I've visited your website and have no doubt you have created an extremely sophisticated tool that can project almost any scenario one could throw at it.

Of course, the problem with projections is that they are based on numbers which are not known - thus, even your model can only be said to be accurate if all forecasted situations occur as predicted. The longer the time horizon, and the more variables that are input, the less likely that will happen for any particular individual.

Thus, ALL models (including your comprehensive tool) are inherently flawed from that perspective - how many of us in 2007 knew about the TFSA? Or the 2009 Federal (and Provincial) Budget changes which affected tax rates?

I remain skeptical that providing as much detail to a modelling forecast as your application allows will reveal any more accurate results for an individual. I liken it to trying to accurately predict the weather where I will happen to be 25 years from now if I know all of the weather data at 100m intervals rather than looking at macroscopic data. I never thought I'd get married and have a child - yet I did, and these are things I have under control yet was not able to accurately predict. So, perhaps it is only me who believes in the fallability of trying to predict the future.

It certainly would be a good idea to attach a disclaimer to each and every spreadsheet posted here to make sure that no one expects too much from them. Something along the lines of:

"These spreadsheets are for your independent use and the author and owners of this website can not and do not guarantee their applicability or accuracy in regards to your particular circumstances. All examples are hypothetical and are for illustrative purposes only."

Thank you for helping to make sure no one mistakenly assumes that these tools have omniscience.

 

[steve41]

Uncertainty is a certainty, however to completely discount tax accuracy is a bit heavy handed. I can go back and run an older version of my program (5 or 6 years say) and apply the tax rules going forward in time and compare them with today's model. You would be surprised how close things are year to year.

The major issue that detractors always dredge up is that no one can estimate what rates (the market) will do over time, so why obsess over getting the tax accurate?

My response is that it doesn't take more than a few key strokes to vary rates (hi/lo/mid) and get three separate projections, or randomize (montecarlo the rates)

When you are making decisions which involve uncertainty, surely you don't suggest that you approximate the underlying logic. Two wrongs don't make a right.

 

[OntFA]

You're better than me... I can't even get the thing to open! I don't understand why it is in the form it is rather than the xls file I have.

xlsx extensions are for excel 2007.

Fixed!

Looks like the last one, on leveraging, is still in excel 2007 format.

Thus, ALL models (including your comprehensive tool) are inherently flawed from that perspective - how many of us in 2007 knew about the TFSA? Or the 2009 Federal (and Provincial) Budget changes which affected tax rates?

The model is not flawed. It is what it is - an estimation tool. What can be flawed is how people use such tools.

 

[cannon_fodder]

xlsx extensions are for excel 2007.



Looks like the last one, on leveraging, is still in excel 2007 format.



The model is not flawed. It is what it is - an estimation tool. What can be flawed is how people use such tools.

Thanks, OntFA - what I don't understand is how I have sent an Excel 2007 file and when the owners of the site post it, it is now a zipped package somewhat deconstructed into XML files. The process seems to only affect Excel 2007 files for some reason. I've forwarded an Excel 2003 version hoping this fixes it.

I believe I know what you are saying about the tools not being flawed. I will counter that it is safer to anticipate that the tools are not complete, or even erroneous, rather than assert they are infallible. I liken it to scientific theory vs. law - a law can be applied in all circumstances over all time and it will always be true, while a theory can never be proven (or else it would become a law) but it can be disproven.

A simple tool will have fewer constraints - "If you invest $X in your TFSA and achieve Y% CAGR you will have $Z after N years." Such a tool would be mathematically correct and could be used, as Steve pointed out, to provide a range of forecasted answers which could provide a guide as to a strategy for investing.

A complex tool that involves hundreds of parameters, many of which are independent, would be more challenging with which to work. With all of the parameters to be considered, how do the authors know which ones are independent and which ones are not? If they are dependent, do they understand the relationships correctly? How are they certain that they have all of the appropriate parameters? How do they know that the ranges of values are correct? How do they properly assign a weighting value to the likelihood of the values? How can the output or reports be properly interpreted to formulate the appropriate strategy? How flexible can the strategy be yet still allow one to reach the intended goal? What implicit assumptions have not been stated?

And, I'm not a psychologist, but it would seem to me that most people who might be presented an opportunity to work with a financial advisor using such a tool would be swayed by the sheer mountain of inputs and parameters. If they are asked to provide 100 data points over various points in time, as opposed to the 4 in the simple tool example, then would it be reasonable to expect, in the absence of an appropriate disclaimer by the financial advisor, that the people would believe it predicts, rather than estimates, their personal outcome? One could be left with a false sense of security.

If I use Naviplan for an example, it appears that at some point along the development cycle, the company felt that there was no need for a disclaimer in their marketing material (e.g. This application is meant for educational purposes only. Please consult a professional financial advisor before making any final financial decisions.). Am I to infer that a financial advisor is no longer necessary? Am I also to infer that financial advisors should not come with disclaimers?

I'm starting to think along the lines of the argument of passive vs. active investing as it pertains to ETFs vs mutual fund managers. Is there commensurate value when employing an extremely detailed planning exercise, whether using something like Naviplan? Or, is a comprehensive, but higher level, assessment and strategic plan completely sufficient and relevant and, because it is easier with which to work, more useful? Perhaps, the answer will only be known when enough empirical data is collected, analyzed and reported.

 

[steve41]

Inclusive/comprehensive tools, while they have the ability to encompass a whole range of data entry elements... RESPs, real estate, pensions, loans.... can be used in a very streamlined manner... say 6 elements:

-age
-salary
-current size of your rrsp
-province
-retirement age
-horizon or 'diebroke' age

Add a rate and an inflation estimate and you have 8.

Now... the fact that the program is computing CPP, OAS, GIS, tax credits, clawbacks, provincial levies, and passing through the complete T1 in order to arrive at a solution isn't the user's concern. All he wants to know is... does the calculation stand up to scrutiny. Can I take these results to an accountant to verify?

It is probably quicker to source up and compute than a simple spreadsheet equivalent, but many users (especial advisers who don't want to have to explain errors and inconsistencies to their clients) seem to prefer the tax accurate tool, as do the DIY users.

 

[cartecredit1212]

good post..and wonderful thread...

Simple

interest calculator simple interest calculation simple interest

​

 

[cannon_fodder]

New Spreadsheet

I didn't realise I could upload any new spreadsheet as long as I zipped it. Here is the one that compares the tax advantages of holding Canadian dividend stocks in an RRSP, TFSA or non-registered portfolio.

 

[CanadianCapitalist]

If you are investing passively in a couch potato portfolio using TD e-Series mutual funds, you'll find this rebalancing spreadsheet pretty handy. Just adjust the current market values of your holdings, the target allocation and how much money you are adding to the portfolio and the spreadsheet spits out how to divide your money between the funds.

Sleepy Portfolio Rebalancing Spreadsheet

 

[andrewf]

The reason why Office 2007 files look like zipped xml files is because this is exactly what they are. This is why zipping a 2007 file also won't actually compress it.

For clarification: if you have an Office 2007 file that looks like a .zip, just change the file extension back to the correct one for the application (like .xlsx or .docx, etc.).

 

[Shayne]

http://www.debtfreeby43.com/category/mortgage-calculators/

Cash Back Mortgage Equivalent Rate Calculator and a Home Purchase Budget Calculator.

 

[Riff Raff]

some really good excel based calculators here:

http://www.vertex42.com/

 

[sonneti]

some really good excel based calculators here:

http://www.vertex42.com/

Are there any good "open office" based calculators?

I think I'm going to have to switch back to excel again :/