I had been using similar functions in parts of my spreadsheet, but as I kept adding more and more nuance and flexibility to the life planning and projection aspects, eventually the formulas just reached such a critical mass that the whole spreadsheet got really painful to maintain. If you feel like kicking the tires on ProjectiFi, I'd be curious to hear your thoughts -- I've been working hard to make everything that had become unwieldy and difficult in Excel for FI planning and experimentation feel more intuitive and straightforward.
Anyway, I don't plan to actually make a product out of mine, but if you're interested in speeding up Monte Carlo financial simulation by literally a million times, let me know :)
Excel is just too slow/not the right tool for MC (or most serious statistics). From my experience with mcmc, compiled languages come in pretty handy to speed up any loop that just can't be vectorized with some linear algebra. So I'd use any tool I'm comfortable with to set up the simulation but delegate the hardcore simulation loops to something like c++.
I guess it's just me who hasn't yet seen any serious mc simulation running on a client I guess :/
The way I see this done whenever it runs fast is using c/c++/java. Another important thing with statistics is the mathematical validity of your results, regardless of whether they look right. For validation, most literature I come across uses c/c++/r.
The potential for going much higher and going down by 90% is in a different league.
That being said, and a disclaimer that I created this, I have been charting my path to being able to retire early. I found it difficult when duplicating tabs to simulate different scenarios and keeping the tabs up-to-date with formula changes. If this speaks to any of you, then consider checking out the tool I made - I have not looked back at my old spreadsheets.
Sample forecast: https://fiers.co/forecast/6020f254b4e8c
Forecast comparison: https://fiers.co/forecasts/compare/6020f254b4e8c/60488472528...
Here are some videos on how to use them.
Perhaps because I'm comfortable writing software it still feels very limiting to work within spreadsheets.
Arguably, the more code works like a spreadsheet (functions transforming values, lists, or matrices) rather than stepwise ‘if this then that’ sequences, the better.
Put another way, logic can be woolier than math, in that concise mathematical formulation tends to removes ambiguity when reasoning over inputs to generate outputs.
Pretty much every bank, brokerage, or financial software I've used is "dumb" and will a) count deposits as growth, b) show total lifetime growth, and/or c) ignore deposits.
XIRR allows me to better benchmark portfolio performance by accounting for when I deposit (or withdraw) money so I can clearly say, "I'm earning X% per year."
Schwab is terrible at this. "You gained 5% today!" No, I didn't, I deposited $1,000 into my brokerage account.
I calculate the modified Dietz return for my other accounts too. It's easy enough that unlike IRR you don't need the function to be built in to the spreadsheet software to calculate it.
I then realized you could also manually calculate XIRR using Goal Seek. Suppose your dates are in D2:D100 and values in V2:V100. Add a cell, let's say I1, to hold an interest rate value, and another column W with formulas defined as follows (filled downward from W2):
Run Goal Seek to find the value of I1 which sets J1 to the value 0. This will give you XIRR. (At least for non-leap years. I'm not sure how leap year logic works.)
Then again, the Ux of Fidelity's site, for example, is also a great example of how not to do things, IMO.
But this is actually equivalent to what I knew as money weighted returns: https://www.investopedia.com/terms/m/money-weighted-return.a...
I've been recommending this course to my family and friends,
For anyone interested, I made a Google Sheets template that I share with my friends. It has been well-received.
The crypto section can be ignored for those not involved with that sector.
If it's useful, would love to hear your feedback.
Of course, much can be automated using Google Finance and relevant pricing APIs to auto-update position values.
The biggest improvement I'd like to make to mine is to implement some approximated form of risk parity. That is, instead of comparing nominal allocations, to compare weighted risk allocations by asset class. This is useful because (for example) equities will contribute significantly more volatility to your portfolio than, say, fixed income, so to the extent you are trying to capture the diversification benefits of allocating across different risk buckets, you may want to scale your exposure according to volatility.
There is a modeling challenge here, of course, because asset classes will never be independent risks, but I'd prefer something directionally indicative rather than econometrically optimal.
Look at the Figure 1 of this paper:
There are broadly 2 regimes that dictate volatility and correlation, normal and shit-hitting-the-fan. Risk parity models skew heavily towards the everyday, when prices tick up / down by small amounts, and diversification exists.
On adverse market wide event, there is (generally) no diversification, and leveraged portfolios in particular can face significant losses.
There is no silver bullet, but portfolio wide value at risk, i.e. what the outcome on the day/week/month on any given day in the last 5 years (or more) had I held these exact same positions is as good a measure as any. The distribution of outcomes being something worth understanding and tuning risk to.
It automatically pulls MSCI market cap information and determines the allocation based on that.
Then the number of shares that you need to buy or sell is calculated based on the target allocation.
If you use Interactive Brokers, then buy and sell texts for the IBOT are also generated.
EDIT: I commented too soon. You have useful info in some hidden columns. Thanks again for this! I'll be incorporating it into my spreadsheet.
Key features ;
- Budget (aka cashflow management, what am I doing with my paycheck next month?)
- Project planning (aka balance sheet, what is the purpose of my inflows and outflows over time?)
- End of year results(aka do my projected budget match my projects, and if you want to travel so much why are you spending X amount on alchool?)
- Taxes (how do I pay less of them)
- Insurance (how do I mitigate common risks, and when should I purchase insurance to protect my projects)
I myself need one app for financial transaction, another for rebalancing my portfolio of ETF, one for doing my taxes, a spreadsheet for my budget, etc. My bank credit card and debit card are managed by two different units which refuse to talk to each other and share information. This is ridiculous.
Offhand, I suspect you could layer all those features you mention, using a mix of GnuCash customizable existing reports, and their (poorly documented) extension in Guile.
The UX might not be what you want with just reports and scripting, but you can definitely analyze data and generate arbitrary noninteractive HTML reports, and there's some limited facility for dialogs that way. (For example, the features to help keep an ITOT & AGG ETF portfolio balanced for risk tolerance seems trivial, and it could even have a sense of calendar schedule for periodic rebalancing. But instead of it doing the rebalancing for you, the UX would probably just be a generated report that shows the balance, and tells you the exact two transactions to rebalance.)
Or, alternatively, get into the code of GnuCash itself, and do whatever interactive GUI and maybe even (if very brave/foolish) making your code talk to your brokerage, etc.
We have built Buxfer to cater to power users, so it has lots of powerful functionality - budgeting, forecasting, automated rules, investment tracking and so on.
GP's feature set sounds like its for "superpower users" :) We don't (and will likely never) get too much into things like taxes. But Buxfer is still one of the rare products that has a simple straightforward pricing and caters to a niche that expects powerful features from their financial software.
Disclaimer: I'm the founder.
Question: Who are you using for Bank sync?
Yodlee? Plaid? Something else?
I'm a mint ->> Personal Capital convert who both use Yodlee, and I'm curious who you use.
Yodlee and SaltEdge
For some reason, many of our users find our bank sync to be more reliable even though everyone uses the same providers underneath. I routinely conduct user interviews and this is a common piece of feedback. I like to think it’s a side effect of us building bank sync in-house in the past (which is a royal PITA to build and maintain). But that somehow let us build a more robust system on top of the same (unreliable) bank data aggregators.
Anyhow feels like I’m rambling so I’ll stop now :)
At this point, I don’t carry collision insurance on our cars (I have liability, of course, but not insurance to pay if we wreck and fault is ours or undetermined.) Why not? Because buying a replacement car wouldn’t be a significant hardship that would be alleviated by the insurance coverage. (Our cars are 2005 and 2015.)
If something you have insurance for wouldn’t be a hardship if it occurred, then the premium should be a rounding error.
To be fair, I guess it could be more annoying to go through the whole insurance process, so I guess I sort of understand what you mean.
Consider tax sheltered accounts (TFSA-RRSP in Canada, 401k in the US). Many people do not understand how they work or even what they are ; a lot of stranger tell me they ''purchased RRSP at their bank last month''.
Also consider the case of freelance consultant. If you are your own business, you must understand what is tax deductible, and what is not.
Finally, different countries have different fiscal law regarding property. A lot of people in Canada invest in real estate because they do not have an easy access to financial markets. Here, capital gains of non primary residence is not tax deductible... oops.
You get the point. The is not financial planning without tax planning. From a dev point of view, consider the fiscal laws of a country like COBOL legacy code. Do not assume it will work as intended.
So you could in many ways reframe "avoiding taxes" as "doing what the government tells you to do".
I think you’ve stated this oddly at a minimum. Why would capital gains on anything be deductible anywhere? Did you mean losses? Or that there’s an exemption amount that you miss for non-primary?
Overpaying taxes (what a lot of people unintentionally do that don't have resources to navigate every nook and cranny of the labyrinthian tax code) accomplishes nothing but make the individual more poor than they already were. Minimizing overpayment is the best possible thing an individual can do.
Others, not so much.
I very much try to follow thr spirit of the law though, not the letter, both in laws I ignore and adhere to.
Not to mention, governments like the US fed will just poof new money out of thin air if they can't afford something anyway.
And... when it comes to tax law, you had better follow the letter not the spirit, otherwise you risk an unpleasant visit from your local tax auditor. Your interpretation of the spirit of the law won't get you out of their crosshairs.
Do people actively look for ways to pay more taxes?
When you put money into a 401K you're shielding income from taxation. A 401K is an explicit tradeoff the government has created; individuals saving more today will hopefully translate into less reliance on the safety net in the future.
If you are American and not reporting the right number of allowances on your W-4, you are giving the gov't a free loan.
> If I have €1000 in an investment that grows with 5% annually and I contribute €100 per month for the next 10 years, how much will accrue? … This can be calculated with the following formula: … FV(5% ÷ 12, 10 × 12, 100, 1000) = -17,175.24
If your investment grows 5% annually then this is the effective rate, not the nominal rate. The monthly interest rate is thus not 5%÷12 but rather ((1+5%)^(1÷12))-1, due to compounding—this can also be written NOMINAL(rate, nper)÷nper. You can see the difference if you compare results with different numbers of periods but no contributions—the result should be the same no matter how the time is divided up.
# Basic formula, 5% annual growth for 10 years with 1-year period
FV(5%, 10, 0, -1000, 0) ⇒ $1,628.89
# As per article, 5% annually for 10 years but with monthly periods
FV(5%/12, 10*12, 0, -1000, 0) ⇒ $1,647.01
# Converting to the nominal rate before division gives consistent results
FV(NOMINAL(5%, 12)/12, 10*12, 0, -1000, 0) ⇒ $1,628.89
Also when trying to remember the tiller app, I found that Excel now has this as part of office 365 https://www.theverge.com/2020/6/16/21292642/microsoft-money-...
My only complaint is that their Google Sheets sidebar UI can be abysmally slow at times, and you have to open it and hit “sync” to load transactions from your financial institutions.
Basically the idea is that financial planning should be done primarily for the long term, and therefore the data does not need to be managed in real time. I manually pull all my account totals once a year, update my model, and make adjustments then. If there’s a significant event (example: onset of a global pandemic) I will do a special edition of this process.
To be clear, I’m contributing to investments through the year, but when doing so I’m following a strategy I check about once a year.
That’s a pretty interesting idea. I wouldn’t take the future earnings = bond concept literally, especially if you’re in a high volatility profession, but it seems useful as a mental check when assessing asset allocations.
Of course this is only true for people who do have access to Social Security. Those who do, can create an account on the Social Security website and generate an estimate of your expected benefit.
I’m an actuary and reasearcher, and the theory is well explained in the academic literature. And you are of course right with respect to the volatility of your future earnings.
Working in tech you can bank alot of money quickly. If you keep your lifestyle low. Then if something happens and everything blows up. If you have enough saved you can get by. Plus alot benefits in the united states are based on income not assets.
I have thought a lot about this. I have been extremely lucky to fall into a decent tech job that is way better than working for my local university a few years ago.
Our current spending for a year with so and I is around 60k a year.
The 4 percent rule says that is about 1.5 million or so. Not there yet plus I like working so far.
But if I have say 500k saved and something happens. I can withdraw around 20k a year. But if let's both of us got minimum wage jobs. So 30 an hour total we can cover that gap.
Obviously, I can't speak for everyone reading this, but if you're getting pinged by recruiters regularly right now, I'd bet that you'll be able to find something within 6 months even in the next recession.
From the FAQ, when asked if there was an Excel version:
For those keeping score at home, you’ll know that I have a preference for excel over Google Sheets. However, I haven’t been able to find a reliable way of getting current and historical stock/ETF price data to import automatically into excel.
While Office 365 has a new “Stocks” feature that lets you import real-time stock prices into excel, this unfortunately doesn’t work for historical prices (yesterday, last month, last year’s price, etc).
This spreadsheet uses historical pricing info to calculate portfolio performance between any two dates, so historical prices are a critical input.
I'm finding it hard to believe that finance professionals don't have some kind of programmatic access to "current and historical stock/ETF price data." Does anyone know? I'd be willing to pay a reasonable "individual investor" level fee.
These formulas are great! I am currently working on adding more charts, graphs, and formulas to my product. These formulas and handy to know about for a net worth sheet. Thanks for sharing!
A guide is here: https://blog.aawadia.dev/2020/12/01/finance-concepts-go-fina...
Sometimes you don't want the first few characters, or the last few, you just really want the ones in the middle.
Sorta more or less models and validates the 4% rule.
And I have another sheet to track all my investments, etc using the GOOGLEFINANCE function to update with the market.
I've also looked at using something like Plaid to import banking/credit card data, but it was all too complicated. So I now get a daily balance update via email that I scrape into my sheet using a Python script... Keeps me from needing to manually update the balances myself.
Demo on Google Sheets:
In ten years, you have no idea what tax rates will be. But you can be pretty confident the Fed will have devalued money by 30%+.
Even if you just want to have tax brackets adjust to inflation - this function gets to be really complicated.
It's simpler, and Lotus 1-2-3 doesn't have MATCH! :-)
I think something like this would work...
I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh
I suspect you could do it with SUMPRODUCT too if the tax table contains sufficient data (e.g. for each band a lump-sum + progressive rate may be necessary) but it may still be an array equation (ctrl-shift-enter when entered, with curly braces displayed around it). I’m not in front of a PC so I can’t try to confirm.
Helped me compare different approaches to balancing paying off the mortgage vs investing.
We currently support the functions listed here  - and allow you to write spreadsheet formulas that get transpiled directly to Python code!
If y'all have any other reccomendations for functions we should add - let me know!
Given 40% inflation.
An item is offered for $1200 in 12 "zero interest" payments of $100 or $800 in cash.
How do you compare their real cost taking into account inflation?
Assuming the $100 payments are paid at the end of each month, the value of the money sent over in today's dollars is $((100/c^1) + (100/c^2) + (100/c^3) + ... + (100/c^12)) which is a geometric series with ratio 1/c, so the sum is (100/c^1 - 100/c^(12+1))/(1-1/c) ≈ $1005.
So take the cash offer.
For spreadsheet calculations, look into the SERIESSUM flavor of macros.
(though from an academic point of view you are completely correct)
Most of what separates a power user from an average joe is having an approach to the problem and laying the foundation in a way to be a “model”.
I’m seen as a power user even within my peer group of finance folks. I just have a way of laying things out and breaking up the problem from data/inputs to print ready outputs. I’m not usually doing anything fancy like using obscure formulas. But when I do, I like these;
* Use -- to convert Boolean to integer
* Use index/match instead of vlookup (you can more easily insert/delete columns without breaking)
* make yourself a style guide (so you know what a color means; hard value, input, etc)
* avoid volatile functions. Learn what functions are volatile
* get good at auditing formulas/debugging. It's really just takes experience