Canadian Money Forum banner

Excel Based Financial Calculators

2M views 49 replies 33 participants last post by  depassp 
#1 ·
#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.
 
#7 ·
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...
 
#8 ·
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.
 
#9 ·
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.
 
#10 · (Edited)
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.
 
#13 ·
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.
 
#16 ·
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
 
#17 · (Edited)
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.).
 
#26 · (Edited)
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.
 
#31 ·
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!
 
#27 ·
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.
 

Attachments

#28 ·
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.
 
#29 ·
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.
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top