Excel Based Financial Calculators

# Thread: Excel Based Financial Calculators

1. ## Excel Based Financial Calculators

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.

2. 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.

3. 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?

4.

5. Originally Posted by 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?
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.

6. Fixed!

7. Thanks!

8. 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...

9. 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.

10. Originally Posted by 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.
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.

11. 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.

Page 1 of 5 123 ... Last

#### Posting Permissions

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