10 ms·

Spreadsheet formulas for personal finance

616 points by bouk 5y ago

isthis129283 5y ago
I love mid. Sometimes you don't want the first few characters, or the last few, you just really want the ones in the middle.
deleted 5y ago
[deleted]
imsd 5y ago
Interesting formulas to take my spreadsheets up a level. 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-P4yH0oOhYdy-CcmBItEc6--50/edit?usp=sharing 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 5y ago
  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. [0] https://en.wikipedia.org/wiki/Risk_parity https://en.wikipedia.org/wiki/Risk_parity [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio/45968.article https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...
  - imsd 5y ago
    That'd be a great addition. I plan to update this with historical charting and tracking of position values over time. I'll see if I can find a way to add risk parity. Makes a lot of sense.
  - xorfish 5y ago
    I would caution against using risk parity as it assumes that you know the volatility and correlation of different asset classes. Look at the Figure 1 of this paper: https://www.casact.org/sites/default/files/old/01pcas_scheel.pdf https://www.casact.org/sites/default/files/old/01pcas_scheel...
    - shapefrog 5y ago
      I would second this - I have no fancy papers or citations etc but eventually risk parity will blow up the world. 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.
- theNJR 5y ago
  Thank you!
- xorfish 5y ago
  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. https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7X_TFkyGRXpy_rR_HondhF10/edit#gid=1421053954 https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7...
  - SeaWhales1000 5y ago
    Very nice. I need something like this incorporated into my workbook since I mainly use ETFs over mutual funds. Not sure if you know, but Sheets has a function "GOOGLEFINANCE" where you can input a ticker symbol and retrieve a lot of useful info, including price. 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.
vlucas 5y ago
Hey - creator of BudgetSheet here (https://www.budgetsheet.net/ https://www.budgetsheet.net/ ). 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 5y ago
  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.
jldugger 5y ago
Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.
- basseq 5y ago
  Because I like a challenge: =SUMPRODUCT( (bracket_min<income)* ( ((income<=bracket_max)*(income-bracket_min)) + ((income>bracket_max)*(bracket_max-bracket_min)) ) *bracket_rate ) 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. Demo on Google Sheets: https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7rFL_TDMWJZpaPxmJD84AD_gg/edit?usp=sharing https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7r...
  - bouk 5y ago
    Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...
    - basseq 5y ago
      You could do it with a bunch of INDEX-MATCH formulas and pre-calculating base tax per bracket: =(income-INDEX(bracket_min,match(income,bracket_min,1)))* INDEX(bracket_tax,match(income,bracket_min,1))+ INDEX(bracket_base_tax,match(income,bracket_min,1))
      - taviso 5y ago
        Clever, but I'm curious why you don't use VLOOKUP? It's simpler, and Lotus 1-2-3 doesn't have MATCH! :-) I think something like this would work... (income-@VLOOKUP(income,table,1))*@VLOOKUP(income,table,3)+@VLOOKUP(income,table,5)
        basseq 5y ago
        Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.
basseq 5y ago
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.
- kccqzy 5y ago
  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.
  - basseq 5y ago
    This is great! Thanks for the info.
    - lotsofpulp 5y ago
      Health equity looks like it has a bunch of unnecessary fees compared to Fidelity (which has no fees). https://www2.healthequity.com/indexinvestor/ https://www2.healthequity.com/indexinvestor/
      - PascLeRasc 5y ago
        Fido fails the XIRR test though. Lively HSA on the other hand passes and has zero fees as well.
- JALTU 5y ago
  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.
senthil_rajasek 5y ago
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 I've been recommending this course to my family and friends, https://www.coursera.org/learn/time-value-of-money https://www.coursera.org/learn/time-value-of-money
captainill 5y ago
Does anyone have a good resource for learning to write beginner through advanced formulas?
- conductr 5y ago
  Not to be snarky but, Just start. Pretty much every excel user is self taught. You can google for the name of the formula that is helpful for your use just like you would programming. 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
- emmelaich 5y ago
  Not quite what you want but try this anyway. "You suck at Excel" by Joel Spolsky https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
- gerbler 5y ago
  You can look at a few of the sheets people link to here and just fiddle around with changing things too to see what happens
the_fire_friar 5y ago
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. Sample forecast: https://fiers.co/forecast/6020f254b4e8c https://fiers.co/forecast/6020f254b4e8c Forecast comparison: https://fiers.co/forecasts/compare/6020f254b4e8c/6048847252895 https://fiers.co/forecasts/compare/6020f254b4e8c/60488472528...
- phonon 5y ago
  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.) Here are some videos on how to use them. Data Table https://www.youtube.com/watch?v=y7S9ecg1wdQ https://www.youtube.com/watch?v=y7S9ecg1wdQ Scenario Manager https://www.youtube.com/watch?v=b_eFIdsV1Bk https://www.youtube.com/watch?v=b_eFIdsV1Bk
  - the_fire_friar 5y ago
    Those are really cool. I didn't know about them (I was also using Google Sheets :|). Perhaps because I'm comfortable writing software it still feels very limiting to work within spreadsheets.
    - Terretta 5y ago
      Think of spreadsheets as an IDE for functional programming so your brain treats them as software. 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.
usrme 5y ago
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/ 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/ https://themeasureofaplan.com/tools/) and is really responsive via email. Be sure to donate if you find value in his work as I did!
- wintermutestwin 5y ago
  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.
  - ptrklly 5y ago
    I think CapIQ is most common, but don't think they do individual subscriptions.
  - drited 5y ago
    The Datastream excel plugin from Refinitiv is one paid option used by professionals. Or if it is just for personal use you could probably scrape yahoo's historical data tables (check their terms of use etc first of course)
mattbillenstein 5y ago
Re FIRE, I built this model some time ago: https://docs.google.com/spreadsheets/d/1E6TjJyowYowexkSvNV8tNCXNvtOGNeYfYUoCmTqFH0E/edit#gid=25893825 https://docs.google.com/spreadsheets/d/1E6TjJyowYowexkSvNV8t... 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.
asdff 5y ago
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.
- jonshariat 5y ago
  You can do this with Tiller (https://www.tillerhq.com/ https://www.tillerhq.com/), it pulls it down for you. 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-in-excel-template-addin-feature-personal-finance-microsoft-365 https://www.theverge.com/2020/6/16/21292642/microsoft-money-...
  - et1337 5y ago
    Tiller user here. It’s a brilliant product. They have many different templates that allow you to manage money at the level of detail you want. I tried a ton of different apps and none of them were flexible enough. 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.
  - asdff 5y ago
    Can I automate with their service? I don't care about their spreadsheet templates, I just want to have a CSV to play with in a local directory that I can update with a cron job.
    - jonshariat 5y ago
      Its been a while but yes you could at least use the sheet as your db intake, they might also have other, cleaner options, but it does pull it down on a regular basis automatically, you dont need to trigger each time.
yboris 5y ago
Slightly-related CLI I created: https://github.com/whyboris/mortgage-and-investments https://github.com/whyboris/mortgage-and-investments Helped me compare different approaches to balancing paying off the mortgage vs investing.
deleted 5y ago
[deleted]
Bostonian 5y ago
Is there a Python package people recommend that has code for these kinds of calculations?
- bouk 5y ago
  numpy-financial does: https://github.com/numpy/numpy-financial/blob/master/numpy_financial/_financial.py https://github.com/numpy/numpy-financial/blob/master/numpy_f...
asadawadia 5y ago
For the developers, there is a really nice go library with these formulae https://github.com/alpeb/go-finance https://github.com/alpeb/go-finance A guide is here: https://blog.aawadia.dev/2020/12/01/finance-concepts-go-finance/ https://blog.aawadia.dev/2020/12/01/finance-concepts-go-fina...
jplr8922 5y ago
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.
- edem 5y ago
  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.
- kachnuv_ocasek 5y ago
  Why do you want to pay less taxes?
  - the_fire_friar 5y ago
    I'd guess to keep more money to use for things that are important to them. Do people actively look for ways to pay more taxes?
  - madamelic 5y ago
    It isn't illegal or even immoral to reduce your taxes to as low as you can legally. You shouldn't pay more taxes than you need to even if you believe in socialism. 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.
  - Alupis 5y ago
marianov 5y ago
High inflation question: 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?
- narush 5y ago
  Are they monthly payments?
  - marianov 5y ago
    Yes
- CaptainNegative 5y ago
  If (annual) inflation is 40%, then the annual discount factor is 1.40 and the monthly one is c = 1.40^(1/12) ≈ 1.0284. 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.
  - medvezhenok 5y ago
    That's computing based on purely the discounted value of money. The more useful metric is not the % of inflation, but rather the % you can get on your money lying around (your answer assumes that to be 40% annually as well - i.e. a real rate of zero, but real rates on all of your options could be deeply negative as well). (though from an academic point of view you are completely correct)
narush 5y ago
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! [1] https://trymito.io/hn https://trymito.io/hn [2] https://docs.trymito.io/how-to/interacting-with-your-data/mito-spreadsheet-formulas https://docs.trymito.io/how-to/interacting-with-your-data/mi...
divbzero 5y ago
> 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.
- qixv 5y ago
  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.
  - thebean11 5y ago
    If you were guaranteed some perpetual income if you were unable to work for any reason (including getting fired and just not being able to find a job) I think it'd be a great model. In the US I think the model is too risky given the huge salaries and our collective precarity.
    - tkojames 5y ago
      Depends though. For example if you are say 30. Own a house with decent mortgage cheaper than rent. If you have let's say more invested than your total debt including mortgage not counting hopefully equity in the house. Plus 2-4 years invested in taxable account. Why not go 100 stock in retirement accounts. 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.
scubakid 5y ago
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: https://projectifi.io/ 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.
- enchiridion 5y ago
  Cool site! If you'd like a bit constructive feedback, the autofolding menus were a bit slow to open and anxiety inducing.
  - scubakid 5y ago
    Do you mean the animation should be faster when you open a new one? If so, good to know -- that'll be a simple tweak. I'm sure I should put more effort into the landing page overall... the reality is that so far I've spent nearly all my time trying to make the core app robust but intuitive.
    - Aeolun 5y ago
      In my opinion, the countdown bar (not sure until what it is counting down) is the anxiety inducing part. I’m trying to read, but my eyes keep skipping to the bar to see if I have enough time left.
      - scubakid 5y ago
        Gotcha. Yeah after the countdown it auto-advances. I hoped that widget might feel playful, but I totally see your point.
- 5y ago
nybble41 5y ago
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
- bouk 5y ago
  Yes this is a good note! I simplified it for the blog post
davchana 5y ago
I also tried to explain one of the formula soup I found for converting numbers like 98 to 3 months, 1 week and 1 day. https://davinder.net/excel-convert-days-to-years-months-weeks-days/ https://davinder.net/excel-convert-days-to-years-months-week...

616 points by bouk 5y ago

I love mid. Sometimes you don't want the first few characters, or the last few, you just really want the ones in the middle.

deleted 5y ago

[deleted]

imsd 5y ago

Interesting formulas to take my spreadsheets up a level. 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-P4yH0oOhYdy-CcmBItEc6--50/edit?usp=sharing 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 5y ago
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. [0] https://en.wikipedia.org/wiki/Risk_parity https://en.wikipedia.org/wiki/Risk_parity [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio/45968.article https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...
- imsd 5y ago
  That'd be a great addition. I plan to update this with historical charting and tracking of position values over time. I'll see if I can find a way to add risk parity. Makes a lot of sense.
- xorfish 5y ago
  I would caution against using risk parity as it assumes that you know the volatility and correlation of different asset classes. Look at the Figure 1 of this paper: https://www.casact.org/sites/default/files/old/01pcas_scheel.pdf https://www.casact.org/sites/default/files/old/01pcas_scheel...
  - shapefrog 5y ago
    I would second this - I have no fancy papers or citations etc but eventually risk parity will blow up the world. 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.
theNJR 5y ago
Thank you!
xorfish 5y ago
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. https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7X_TFkyGRXpy_rR_HondhF10/edit#gid=1421053954 https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7...
- SeaWhales1000 5y ago
  Very nice. I need something like this incorporated into my workbook since I mainly use ETFs over mutual funds. Not sure if you know, but Sheets has a function "GOOGLEFINANCE" where you can input a ticker symbol and retrieve a lot of useful info, including price. 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.

vlucas 5y ago

Hey - creator of BudgetSheet here (https://www.budgetsheet.net/ https://www.budgetsheet.net/ ). 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 5y ago
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.

jldugger 5y ago

Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.

basseq 5y ago
Because I like a challenge: =SUMPRODUCT( (bracket_min<income)* ( ((income<=bracket_max)*(income-bracket_min)) + ((income>bracket_max)*(bracket_max-bracket_min)) ) *bracket_rate ) 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. Demo on Google Sheets: https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7rFL_TDMWJZpaPxmJD84AD_gg/edit?usp=sharing https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7r...
- bouk 5y ago
  Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...
  - basseq 5y ago
    You could do it with a bunch of INDEX-MATCH formulas and pre-calculating base tax per bracket: =(income-INDEX(bracket_min,match(income,bracket_min,1)))* INDEX(bracket_tax,match(income,bracket_min,1))+ INDEX(bracket_base_tax,match(income,bracket_min,1))
    - taviso 5y ago
      Clever, but I'm curious why you don't use VLOOKUP? It's simpler, and Lotus 1-2-3 doesn't have MATCH! :-) I think something like this would work... (income-@VLOOKUP(income,table,1))*@VLOOKUP(income,table,3)+@VLOOKUP(income,table,5)
      - basseq 5y ago
        Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.

basseq 5y ago

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.

kccqzy 5y ago
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.
- basseq 5y ago
  This is great! Thanks for the info.
  - lotsofpulp 5y ago
    Health equity looks like it has a bunch of unnecessary fees compared to Fidelity (which has no fees). https://www2.healthequity.com/indexinvestor/ https://www2.healthequity.com/indexinvestor/
    - PascLeRasc 5y ago
      Fido fails the XIRR test though. Lively HSA on the other hand passes and has zero fees as well.
JALTU 5y ago
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.

senthil_rajasek 5y ago

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 I've been recommending this course to my family and friends, https://www.coursera.org/learn/time-value-of-money https://www.coursera.org/learn/time-value-of-money

captainill 5y ago

Does anyone have a good resource for learning to write beginner through advanced formulas?

conductr 5y ago
Not to be snarky but, Just start. Pretty much every excel user is self taught. You can google for the name of the formula that is helpful for your use just like you would programming. 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
emmelaich 5y ago
Not quite what you want but try this anyway. "You suck at Excel" by Joel Spolsky https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
gerbler 5y ago
You can look at a few of the sheets people link to here and just fiddle around with changing things too to see what happens

the_fire_friar 5y ago

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. Sample forecast: https://fiers.co/forecast/6020f254b4e8c https://fiers.co/forecast/6020f254b4e8c Forecast comparison: https://fiers.co/forecasts/compare/6020f254b4e8c/6048847252895 https://fiers.co/forecasts/compare/6020f254b4e8c/60488472528...

phonon 5y ago
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.) Here are some videos on how to use them. Data Table https://www.youtube.com/watch?v=y7S9ecg1wdQ https://www.youtube.com/watch?v=y7S9ecg1wdQ Scenario Manager https://www.youtube.com/watch?v=b_eFIdsV1Bk https://www.youtube.com/watch?v=b_eFIdsV1Bk
- the_fire_friar 5y ago
  Those are really cool. I didn't know about them (I was also using Google Sheets :|). Perhaps because I'm comfortable writing software it still feels very limiting to work within spreadsheets.
  - Terretta 5y ago
    Think of spreadsheets as an IDE for functional programming so your brain treats them as software. 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.

usrme 5y ago

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/ 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/ https://themeasureofaplan.com/tools/) and is really responsive via email. Be sure to donate if you find value in his work as I did!

wintermutestwin 5y ago
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.
- ptrklly 5y ago
  I think CapIQ is most common, but don't think they do individual subscriptions.
- drited 5y ago
  The Datastream excel plugin from Refinitiv is one paid option used by professionals. Or if it is just for personal use you could probably scrape yahoo's historical data tables (check their terms of use etc first of course)

mattbillenstein 5y ago

Re FIRE, I built this model some time ago: https://docs.google.com/spreadsheets/d/1E6TjJyowYowexkSvNV8tNCXNvtOGNeYfYUoCmTqFH0E/edit#gid=25893825 https://docs.google.com/spreadsheets/d/1E6TjJyowYowexkSvNV8t... 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.

asdff 5y ago

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.

jonshariat 5y ago
You can do this with Tiller (https://www.tillerhq.com/ https://www.tillerhq.com/), it pulls it down for you. 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-in-excel-template-addin-feature-personal-finance-microsoft-365 https://www.theverge.com/2020/6/16/21292642/microsoft-money-...
- et1337 5y ago
  Tiller user here. It’s a brilliant product. They have many different templates that allow you to manage money at the level of detail you want. I tried a ton of different apps and none of them were flexible enough. 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.
- asdff 5y ago
  Can I automate with their service? I don't care about their spreadsheet templates, I just want to have a CSV to play with in a local directory that I can update with a cron job.
  - jonshariat 5y ago
    Its been a while but yes you could at least use the sheet as your db intake, they might also have other, cleaner options, but it does pull it down on a regular basis automatically, you dont need to trigger each time.

yboris 5y ago

Slightly-related CLI I created: https://github.com/whyboris/mortgage-and-investments https://github.com/whyboris/mortgage-and-investments Helped me compare different approaches to balancing paying off the mortgage vs investing.

deleted 5y ago

[deleted]

Bostonian 5y ago

Is there a Python package people recommend that has code for these kinds of calculations?

bouk 5y ago
numpy-financial does: https://github.com/numpy/numpy-financial/blob/master/numpy_financial/_financial.py https://github.com/numpy/numpy-financial/blob/master/numpy_f...

asadawadia 5y ago

For the developers, there is a really nice go library with these formulae https://github.com/alpeb/go-finance https://github.com/alpeb/go-finance A guide is here: https://blog.aawadia.dev/2020/12/01/finance-concepts-go-finance/ https://blog.aawadia.dev/2020/12/01/finance-concepts-go-fina...

jplr8922 5y ago

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.

edem 5y ago
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.
kachnuv_ocasek 5y ago
Why do you want to pay less taxes?
- the_fire_friar 5y ago
  I'd guess to keep more money to use for things that are important to them. Do people actively look for ways to pay more taxes?
- madamelic 5y ago
  It isn't illegal or even immoral to reduce your taxes to as low as you can legally. You shouldn't pay more taxes than you need to even if you believe in socialism. 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.
- Alupis 5y ago

marianov 5y ago

High inflation question: 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?

narush 5y ago
Are they monthly payments?
- marianov 5y ago
  Yes
CaptainNegative 5y ago
If (annual) inflation is 40%, then the annual discount factor is 1.40 and the monthly one is c = 1.40^(1/12) ≈ 1.0284. 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.
- medvezhenok 5y ago
  That's computing based on purely the discounted value of money. The more useful metric is not the % of inflation, but rather the % you can get on your money lying around (your answer assumes that to be 40% annually as well - i.e. a real rate of zero, but real rates on all of your options could be deeply negative as well). (though from an academic point of view you are completely correct)

narush 5y ago

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! [1] https://trymito.io/hn https://trymito.io/hn [2] https://docs.trymito.io/how-to/interacting-with-your-data/mito-spreadsheet-formulas https://docs.trymito.io/how-to/interacting-with-your-data/mi...

divbzero 5y ago

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

qixv 5y ago
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.
- thebean11 5y ago
  If you were guaranteed some perpetual income if you were unable to work for any reason (including getting fired and just not being able to find a job) I think it'd be a great model. In the US I think the model is too risky given the huge salaries and our collective precarity.
  - tkojames 5y ago
    Depends though. For example if you are say 30. Own a house with decent mortgage cheaper than rent. If you have let's say more invested than your total debt including mortgage not counting hopefully equity in the house. Plus 2-4 years invested in taxable account. Why not go 100 stock in retirement accounts. 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.

scubakid 5y ago

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: https://projectifi.io/ 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.

enchiridion 5y ago
Cool site! If you'd like a bit constructive feedback, the autofolding menus were a bit slow to open and anxiety inducing.
- scubakid 5y ago
  Do you mean the animation should be faster when you open a new one? If so, good to know -- that'll be a simple tweak. I'm sure I should put more effort into the landing page overall... the reality is that so far I've spent nearly all my time trying to make the core app robust but intuitive.
  - Aeolun 5y ago
    In my opinion, the countdown bar (not sure until what it is counting down) is the anxiety inducing part. I’m trying to read, but my eyes keep skipping to the bar to see if I have enough time left.
    - scubakid 5y ago
      Gotcha. Yeah after the countdown it auto-advances. I hoped that widget might feel playful, but I totally see your point.
5y ago

nybble41 5y ago

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

bouk 5y ago
Yes this is a good note! I simplified it for the blog post

davchana 5y ago

I also tried to explain one of the formula soup I found for converting numbers like 98 to 3 months, 1 week and 1 day. https://davinder.net/excel-convert-days-to-years-months-weeks-days/ https://davinder.net/excel-convert-days-to-years-months-week...