Wow this is like a flashback to my own experiments in Excel, prior to going full mad-scientist for the past 6 months and creating a web app for FI/FIRE planning called ProjectiFi:
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.
Love these formulas and wish this information was more understood and accessible to people when making decisions. I've personally benefited a lot from a mortgage payoff spreadsheet I have. It's so easy to duplicate a tab, change the interest rate or additional payments and see what the long term impact is.
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.
Have you looked at the "What-if Analysis" functionality in Excel? Scenario Manager and Data Table is designed for that kind of use case (one set of formulas, multiple sets of inputs/scenarios.)
I like XIRR to get an actual sense of what my annualized portfolio performance looks like.
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.
HealthEquity, where I keep my HSA account, manages to get this right. They use modified Dietz return which is easier to calculate than the IRR but remarkably similar.
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.
For folks who don't know: XIRR looks at a list of "deposit"/"withdrawals" and their dates and tries to calculate the equivalent of a fixed "interest rate" that would supply the same return for the same cash flows. So if you have a portfolio that you added and withdrew money from it calculates a % that a fixed interest savings account would need to deliver the same cash flow. Brokers are typically paid by commission/activity and thus are not incentivized to tell you the cold hard truth. XIRR is honest about how super your stonks are.
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):
=$V2*(1+$I$1)^(($D$100-$D2)/365)
Then make a cell J1 with formula =SUM(W2:W100).
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.)
Personal finance is simple, financial planning (forecasting/scenario planning) is hard, and building software to "teach" this to customers is vastly harder and not really in the wheelhouse of financial institutions. This isn't to defend such companies, nor to knock developers, just to point out that it's very difficult to meet people where they are on this very personal topic.
Then again, the Ux of Fidelity's site, for example, is also a great example of how not to do things, IMO.
While this spreadsheet and formulas are useful I found taking an Intro to Finance course to be more useful. Especially, because I have a purely engineering background
This looks great; it nearly identical to a sheet I developed for my personal use.
The biggest improvement I'd like to make to mine is to implement some approximated form of risk parity[0]. 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[1].
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.
Here is my sheet that is mainly used for rebalancing ETFs according to some simple rules. It is currency agnostic (base currency can be changed) and should work fine for European investors as well.
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.
I have an MSC in quant finance, and i'm seriously considering learning Django (in top of my backend skills) to build an complete app for personal finance management.
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.
For a non-startup personal open source project, you can consolidate all that data in the desktop program https://gnucash.org/ .
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.
There is a dire need for a good personal finance app. Mint is just out there to grab data, YNAB is ok but super limited. And then pretty much every bank site is lacking in any meaningful tools and API access.
I want to do the same, but I don't have the math knowledge. I have the programming knowledge though. Do you want to collaborate? Feel free to send a DM if you do.
This is a nice article, but it ignores the difference between nominal and effective interest rates. Simple division only works on nominal interest rates, but you are more likely to encounter effective rates which need to be converted first.
> 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
I wish it was easier to actually pull information from various banks and accounts. For all of mine, I have to log in to the website and click around some 2006 era website to set a range or dates or something before I can generate a CSV file. It's a ton of friction just to get a look at my financials. I wish I could always have access to updated CSV files without having to spend all this time for each and every account. The only alternative is paid services like mint, which I don't want. I just want my raw data so I can roll my own and come up with my own ways to manage my finances.
Excel[0] has this functionality built in via Plaid[1]. There are other solutions like YNAB[2] or Wealthfront[3]. I agree with you though. I have a nice python program for understanding my finances, but I still have to manually update bank/credit card/loan balances.
I take this as a positive in my personal financial planning.
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.
> An controversial book on investing I recently read is Lifecycle Investing which argues that young people are often much too conservative in their investment strategy, because they should model their future earnings as a bond and allocate enough to equities to compensate for holding this bond, even leveraging up to 100% to get the right allocation.
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.
You can do the same thing with Social Security. It’s basically a forced annuity, so your private investing strategy can be more aggressive than if you did not have access to Social Security.
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.
This is not controversial, at least not in Denmark. All big pension providers invests your money in this way (unless you explicitly drop out).
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.
How long would you expect someone in a high volatility profession to be out of work for? The logic would really only be flawed if someone became long term unemployed or didn't have enough cash reserve to cover a few months of temporary unemployment and hence was risking selling equities from savings at a time of unemployment (which could well correlate with low equity valuations)
These are awesome and exactly the kind of formulas I wish I would have been taught earlier on my life! I've personally been using a free spreadsheet-based tool called the Investment Portfolio Tracker by The Measure of a Plan (https://themeasureofaplan.com/investment-portfolio-tracker/) and it's been absolutely wonderful; it never ceases to amaze me how much power spreadsheets have. The author has other tools as well (https://themeasureofaplan.com/tools/) and is really responsive via email. Be sure to donate if you find value in his work as I did!
This looks so awesome, but there is no way that I am putting this kind of personal data into the cloud (and am particularly not wanting to feed Google's stalker AI).
From the FAQ, when asked if there was an Excel version:
-------------------
Unfortunately not.
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!
I use Tiller for this. By initial comparison this looks more expensive with less features and a limit on total accounts, am I missing something? Tiller is a bit weak on tracking stocks in investment accounts.
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.
Where income is your income, bracket_min is the range of bracket minimums, bracket_max is the range of bracket maximums, and bracket_rate is the range of bracket tax rates.
I think you could probably do this in a single formula with SUMPRODUCT, but right now I just have a table that uses MIN(0, X) and MAX(0, Y) tricks to calculate it.
Some form of the golden duo of INDEX and MATCH should do it in Excel.
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.
Very cool! I really appreciate this list. I'm currently evaluating what spreadsheet functions to add to my spreadsheet product [1], and I'm gonna add these to the roadmap.
We currently support the functions listed here [2] - 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!
[+] [-] scubakid|4 years ago|reply
https://projectifi.io/
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.
[+] [-] the_fire_friar|4 years ago|reply
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...
[+] [-] phonon|4 years ago|reply
Here are some videos on how to use them.
Data Table https://www.youtube.com/watch?v=y7S9ecg1wdQ
Scenario Manager https://www.youtube.com/watch?v=b_eFIdsV1Bk
[+] [-] basseq|4 years ago|reply
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.
[+] [-] kccqzy|4 years ago|reply
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.
[+] [-] bunabhucan|4 years ago|reply
[+] [-] SamReidHughes|4 years ago|reply
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):
Then make a cell J1 with formula =SUM(W2:W100).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.)
[+] [-] JALTU|4 years ago|reply
Then again, the Ux of Fidelity's site, for example, is also a great example of how not to do things, IMO.
[+] [-] dv_dt|4 years ago|reply
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] senthil_rajasek|4 years ago|reply
I've been recommending this course to my family and friends, https://www.coursera.org/learn/time-value-of-money
[+] [-] imsd|4 years ago|reply
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.
https://docs.google.com/spreadsheets/d/1qYLOAjzaIIcFLFw_j-P4...
Of course, much can be automated using Google Finance and relevant pricing APIs to auto-update position values.
[+] [-] settrans|4 years ago|reply
The biggest improvement I'd like to make to mine is to implement some approximated form of risk parity[0]. 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[1].
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.
[0] https://en.wikipedia.org/wiki/Risk_parity [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...
[+] [-] xorfish|4 years ago|reply
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.
https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7...
[+] [-] theNJR|4 years ago|reply
[+] [-] jplr8922|4 years ago|reply
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.
[+] [-] neilv|4 years ago|reply
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.
[+] [-] 9erdelta|4 years ago|reply
[+] [-] gerdesj|4 years ago|reply
https://www.firefly-iii.org/
[+] [-] scrollaway|4 years ago|reply
[+] [-] edem|4 years ago|reply
[+] [-] Aeolun|4 years ago|reply
Basically always?
[+] [-] kachnuv_ocasek|4 years ago|reply
[+] [-] nybble41|4 years ago|reply
> 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.
[+] [-] bouk|4 years ago|reply
[+] [-] asdff|4 years ago|reply
[+] [-] shepherdjerred|4 years ago|reply
[0] https://plaid.com/blog/microsoft-announcement/
[1] https://plaid.com/
[2] https://www.youneedabudget.com/
[3] https://www.wealthfront.com/
[+] [-] jonshariat|4 years ago|reply
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-...
[+] [-] snowwrestler|4 years ago|reply
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.
[+] [-] divbzero|4 years ago|reply
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.
[+] [-] snowwrestler|4 years ago|reply
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.
[+] [-] qixv|4 years ago|reply
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.
[+] [-] drited|4 years ago|reply
[+] [-] usrme|4 years ago|reply
[+] [-] wintermutestwin|4 years ago|reply
From the FAQ, when asked if there was an Excel version:
-------------------
Unfortunately not.
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.
[+] [-] vlucas|4 years ago|reply
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!
[+] [-] kraig|4 years ago|reply
[+] [-] asadawadia|4 years ago|reply
A guide is here: https://blog.aawadia.dev/2020/12/01/finance-concepts-go-fina...
[+] [-] isthis129283|4 years ago|reply
Sometimes you don't want the first few characters, or the last few, you just really want the ones in the middle.
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] mattbillenstein|4 years ago|reply
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.
[+] [-] jldugger|4 years ago|reply
[+] [-] basseq|4 years ago|reply
Demo on Google Sheets:
https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7r...
[+] [-] bouk|4 years ago|reply
[+] [-] whateveracct|4 years ago|reply
I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh
[+] [-] NamTaf|4 years ago|reply
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.
[+] [-] davchana|4 years ago|reply
https://davinder.net/excel-convert-days-to-years-months-week...
[+] [-] yboris|4 years ago|reply
Helped me compare different approaches to balancing paying off the mortgage vs investing.
[+] [-] narush|4 years ago|reply
We currently support the functions listed here [2] - 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!
[1] https://trymito.io/hn [2] https://docs.trymito.io/how-to/interacting-with-your-data/mi...
[+] [-] marianov|4 years ago|reply
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?