# Sticky  Excel Based Financial Calculators



## 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

high octane said:


> 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

steve41 said:


> 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

cannon_fodder said:


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



FrugalTrader said:


> Fixed!


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



cannon_fodder said:


> 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

OntFA said:


> 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

Riff Raff said:


> 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 :/


----------



## Sustainable PF

sonneti said:


> Are there any good "open office" based calculators?
> 
> I think I'm going to have to switch back to excel again :/


You should be able to convert the .xls into the OO format I would think ...


----------



## HaroldCrump

If you have Office 2007 or above, try saving as XLSX format.
That is the "open" format and should work with other spreadsheet programs.


----------



## Alfred

Hi All,
I was looking for these. I even asked people from them but didn't get proper response.
Thanks I got them in this forum.


----------



## bigray

Thanks! just what i needed...


----------



## nahanni

*Excel based investment analysis*

Thanks for the calculators Cannon_Fodder. Any chance of viruses in these before I enable the macros?


----------



## Barwelle

*Investment Tracking Spreadsheet*

Okay folks... I have been working on an Excel workbook to track stocks. It is a lot more complicated than I thought it would be to do this!

I'm posting it here so that others could use it. The only other one I've been able to come across is KaeJS', but since I do enjoy playing with Excel, thought I'd give a shot at it myself. There is a phoney account inputted already for demonstration.

The workbook is far from perfect, but it will work for me, for now. I don't have anything for options (as I don't fully understand them yet), share splits/reverse splits, things like that. There is no space for keeping track of taxes payable, though you should be able to figure it out using the dividend total and net profit columns in the ledger tab. Also, contributing and withdrawing cash part-way through the year throws off some of the gain/loss % calculations.

I am open to suggestions for how to improve it, and if there is enough interest I will update and create new versions for your use, then you can just copy/paste your info from the old tracking workbook into the new one. Maybe it is a good idea to subscribe to this thread so that you know when an updated version comes along, I really don't know yet how far this is going to go though.

v1.0 is a .xlsx file for Excel 2007 and 2010.

View attachment 90


v1.0a is a .xls file for Excel 97-2003. *I cannot guarantee this will work properly, as I made the workbook in Excel 2010!*

View attachment 91


I will post instructions on how to use the live quote sheet within the next few days. If anyone else knows an easier way to get live quotes, please share.


----------



## Max

I like the spreadsheet, but needs a little importing quotes. Try this. Just keep adding stock tickers down column A (unlimited).

It downloads from Yahoo, which doesn't have perfect source data, so be careful in trusting the data as you would be careful in trusting yahoo data on their website. Lookup the correct ticker value there.

Only works with Excel 2007.


----------



## Barwelle

Max, thanks for the tip, however it won't work for me. My computer won't let me run the macros due to security issues. Do you know how to create the macro to do this? I'm not too familiar with them. Ideally, I'd like to have something that will pull only the current market price straight into the cell on my "holdings" sheet.


----------



## Max

Sub GetQuote()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://download.finance.yahoo.com/d/quotes.csv?s=" & "IBM" & "&f=l1"
Do
DoEvents
Loop Until Not .busy
Do
DoEvents
Loop Until .Document.readystate = "complete"
Cells(1, 1) = .Document.body.innertext
End With
ie.Quit
End Sub

Cutting it down as simple as possible, this will get the current price for IBM into cell A1. Use a variable for the ticker symbol and change cells(1,1) to desired output range.

This should now work with earlier versions of Excel as well.


----------



## Andrew

Can we add this DCF stock model to the list?

http://www.canadiancorner.ca/files/file/1-discounted-cash-flow-stock-valuation-model/


----------



## Quotealex

Barwelle said:


> v1.0 is a .xlsx file for Excel 2007 and 2010.
> 
> View attachment 90
> 
> 
> v1.0a is a .xls file for Excel 97-2003. *I cannot guarantee this will work properly, as I made the workbook in Excel 2010!*
> 
> View attachment 91
> 
> 
> I will post instructions on how to use the live quote sheet within the next few days. If anyone else knows an easier way to get live quotes, please share.


It looks like both attachments are invalid!


----------



## Barwelle

In case anyone else is interested, I took the spreadsheets down because there wasn't any interest in it but if somebody is, PM me.


----------



## brad

Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?


----------



## CanadianCapitalist

brad said:


> Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?


I don't think there is. You could process the table in this page to get inflation data for Canada but of course, if BoC changes the table, you'll have to change the code too.

http://www.bankofcanada.ca/rates/price-indexes/cpi/


----------



## brad

Okay thanks!


----------



## Spudd

brad said:


> Question: Is there a formula to show current inflation rates (updated automatically online) for a given country using Google Docs or Excel, similar to the formulas we can use to get automatically updating stock prices?


For Google Docs you can use: =GoogleFinance("CURRENCY:USDCAD" ; "average")


----------



## brad

Spudd said:


> For Google Docs you can use: =GoogleFinance("CURRENCY:USDCAD" ; "average")


Thanks, but I don't think that's an inflation rate, right? It looks like a currency exchange rate or something.

I was looking for something that could display current inflation rates for a particular country and update them automatically.

I have historical average rates here: http://data.worldbank.org/indicator/FP.CPI.TOTL.ZG


----------



## Spudd

brad said:


> Thanks, but I don't think that's an inflation rate, right? It looks like a currency exchange rate or something.


Oh, for some reason I thought you wanted exchange rate. Just ignore me!


----------



## marina628

I found this Rent vs Buy calculator today and has quite a bit of detail so thought I would share.Rent vs Buy Calculator


----------



## Marguerite Gilmore

*Financial Calculator*

Hi, really thanks for giving this.


----------



## rateshoppemortgages

Create your own financial plan, analyse mutual funds, and take charge of your financial needs with wide range of free, comprehensive *Mortgage Analyzer* is working as Financial Calculators (This is based on my understanding of PPF rules.), it create an amortization schedule and Calculate mortgage payment. Find what you will owe in next 5 years on Rateshoppe.ca


----------



## Shayne

http://www.mortgagecalculatortoolkit.com/


----------



## Bobjones

*Gold*

Have you considered saving some of your money in gold? Goldmoney provides fully allocated gold ownership and free storage.


----------



## Allen Robinson

*allen*

Hi 
Welcome to this forum site, here you can find lots of thing, I suggest you for your problem you have to contact with your friends, I m not able to give you answer right now, sorry for that.
----------------------


----------



## OnlyMyOpinion

Wow, another old thread has been woken from its deep summer slumber!
It makes me think that _"there must be something in the water"_ these days.
That in turn makes me think of one of my favorite highway drivin' songs by Pokey LaFarge.
Here it is for your viewing pleasure - as unrelated and unhelpful to this thread as Allen's post is I'm afraid


----------



## OnlyMyOpinion

Gee lisakent, glad you liked Pokey's video. 
I see that like the earlier "Allen Robinson" you have posted meaningless replies to four threads.
Should we expect your spam posting sometime soon?


----------



## rachelsmith

Thanks for sharing!


----------



## hfp75

The links on the first post are broken....


----------



## micheal_tye

Riff Raff said:


> some really good excel based calculators here:
> 
> Excel Templates, Calendars, Calculators and Spreadsheets by Vertex42


I think vertex42 has many featured financial calculators. I have gone through some of them and they are really cool.


----------



## depassp

Is it possible to refresh the links to the calculators?


----------

