Spreadsheet formulas for personal finance | Hacker News Reader

Spreadsheet formulas for personal finance

bou.ke

616 pointsbouk117 comments

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/

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.

Cool site! If you'd like a bit constructive feedback, the autofolding menus were a bit slow to open and anxiety inducing.

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.

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.

Gotcha. Yeah after the countdown it auto-advances. I hoped that widget might feel playful, but I totally see your point.

Could you make it so that there is not only a blank page, in case one does not enable JS blindly on every never before visited website?

Good call, I should have thought of that.

Thanks, I also tend to have JS disabled, and saw that you added a note now.

Have you gotten much traction on the subscriptions? I made some financial simulations for my own use and was thinking about ways to monetize, but I dismissed subscriptions as I didn't feel there would be a big market. I'd love to be wrong about that.

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

It does seem a bit niche, but there's been modest traction so far. And sure, I'd be curious to hear how your Monte Carlo implementation might differ. Feel free to reach out on email/discord if you'd like to chat more.

> And sure, I'd be curious to hear how your Monte Carlo implementation might differ.

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

Agreed on Excel not being a suitable tool for MC; that's one of the many things that pushed me in the direction of building ProjectiFi. Within ProjectiFi, I'm sure there are some things that could be done to speed them up further (e.g. offloading them to a backend where they can run faster), but there could also be some trade-offs with that -- for one, it would violate the "everything stays client-side by default" principle mentioned in another comment.

> I'm sure there are some things that could be done to speed them up further (e.g. offloading them to a backend where they can run faster), but there could also be some trade-offs with that -- for one, it would violate the "everything stays client-side by default" principle

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.

Hey, could you add support for cryptocurrency in investments? So that I could enter how much I have in cryptocurrency instead of $USD, and it would automatically calculate it to $.

Wouldn't that be a real crapshoot anyway, if you're thinking in the timeframes relevant for financial independence? Surely you're not expecting your crypto portfolio to predictably appreciate by 6-8% per year over a 10 year timeframe with large variance.

The potential for going much higher and going down by 90% is in a different league.

For now I tend to agree with marvin here, but I may add something for this eventually. If you already know of some good APIs for grabbing the latest conversion rates, let me know. I want ProjectiFi to feel like it can quickly model most of your what-if scenarios, so support for different asset classes might make sense here, along with customizable growth rates over time (e.g. perhaps the same kind of interactive plot like the stock/bond allocation widget but for playing around with different potential crypto growth rates or something).

Are you planning to add a self-hosted option? My one thing about all these finance SaaS is that I'm sure part of their business model is selling your data, even if anonymised. I'd be happy to pay a one-off fee and self-host it or something.

Selling data is not part of mine. In fact, once the page loads you should be able to turn off your internet connection and have all the planning/simulation features still work. Everything stays client-side by default, the only way plan data is ever transmitted anywhere is if you upgrade to Premium and then choose to enable cloud synchronization (which is one of 3 different data persistence methods).

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

Forecast comparison: https://fiers.co/forecasts/compare/6020f254b4e8c/60488472528...

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

Scenario Manager https://www.youtube.com/watch?v=b_eFIdsV1Bk

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.

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.

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.

This is great! Thanks for the info.

Health equity looks like it has a bunch of unnecessary fees compared to Fidelity (which has no fees).

https://www2.healthequity.com/indexinvestor/

Fido fails the XIRR test though. Lively HSA on the other hand passes and has zero fees as well.

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.

Also annoying, most stock trackers in brokerages and elsewhere really don’t track dividend payments as performance either.

FWIW, my brokerage (Raymond James) does this right.

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.

Thanks, I wanted that and just used 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):

    =$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.)

I was trying to figure what this XIRR does: XIRR is the Excel function computing the IRR, which I didn't know about: https://www.investopedia.com/terms/i/irr.asp

But this is actually equivalent to what I knew as money weighted returns: https://www.investopedia.com/terms/m/money-weighted-return.a...

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

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

Of course, much can be automated using Google Finance and relevant pricing APIs to auto-update position values.

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 [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...

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.

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

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.

Thank you!

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

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.

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.

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.

Why do you want to pay less taxes?

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?

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.

It is not somehow patriotic to pay more taxes than one legally owes.

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.

There are governments that I happily overpay to because they’re actually a net good to the people.

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.

Unless your extra generous contributions are in the 10's of thousands, hundreds of thousands, or millions of dollars per year (depending on which government you're referring to - local, state, fed), I can assure you, whatever money you send to whatever government is not significant enough to make any difference. You're far better off sending that money directly to vetted non-profits that do the things you think your area needs more of.

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.

Its not that I want to completely avoid them. Its that tax laws of many countries are poorly written (or applied) and not doing optimization is financial suicide.

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.

I'm generally pro tax if that's a thing, but it is worth noting that many countries use tax policy to shape incentives. If everyone ignored that incentive structure and just paid the tax, then there'd be more cigarette smokers and less EV drivers.

So you could in many ways reframe "avoiding taxes" as "doing what the government tells you to do".

> capital gains of non primary residence is not tax deductible.

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?

Paying less in taxes doesn't have to be nefarious.

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.

Send me an email if you're looking for work :)

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.

If you dislike Mint's business model and find YNAB limited, you might like Buxfer (https://www.buxfer.com)

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.

Decent price and looks reasonably featureful. Good job!

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.

ah, found it here for others who might be interested:

https://www.buxfer.com/help/security

Yodlee and SaltEdge

Yes that’s right. Most of US bank coverage is through Yodlee. We got started with them and have stuck with them so far. Tinkered with Plaid a little but Yodlee’s per-user pricing works better for us than Plaid’s per-account pricing (power users tend to have lots of accounts). Haven’t revisited this in a while so things might have changed but it’s a pain to switch data providers so the benefit has to be very tangible.

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

I have been using Lunch Money[0] after it was mentioned here and am pretty happy with it. It took a while for the API[1] to launch but it's in beta at the moment and they're pretty responsive to user feedback.

[0] https://lunchmoney.app [1] https://lunchmoney.dev

This is well thought of in that sphere:

https://www.firefly-iii.org/

> when should I purchase insurance to protect my projects

Basically always?

Disagree. I take the view that you should only insure against risks that would be significant hardships if they occurred. (Maybe a secondary exception if insurance buys you significant peace of mind.)

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

Wouldn’t that mean that the premium on that insurance is also practically zero?

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.

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.

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

Yes this is a good note! I simplified it for the blog post

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.

You can do this with Tiller (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-...

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.

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.

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.

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.

[0] https://plaid.com/blog/microsoft-announcement/

[1] https://plaid.com/

[2] https://www.youneedabudget.com/

[3] https://www.wealthfront.com/

If you’re already writing Python, check out ofxtools [1]. Pretty easy to use, and csingley has done a great job modeling the OFX specs, complete with type hints!

[1] https://github.com/csingley/ofxtools

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.

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.

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.

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.

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)

I found it comforting to realize that long-term unemployment is quite unusual, less than 6% of the unemployed (who, even in the depths of a recession, are low double digits) for 'Professional and Business Services': https://fivethirtyeight.com/features/the-biggest-predictor-o...

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.

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.

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.

I think CapIQ is most common, but don't think they do individual subscriptions.

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)

Hey - creator of BudgetSheet here (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!

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.

For the developers, there is a really nice go library with these formulae https://github.com/alpeb/go-finance

A guide is here: https://blog.aawadia.dev/2020/12/01/finance-concepts-go-fina...

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.

Re FIRE, I built this model some time ago: 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.

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.

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.

AFAICT, SUMPRODUCT is insufficient, you need to provide different numbers to each row. I need a fold_left version of sumproduct.

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.

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

Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...

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

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)

Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.

The biggest problem with this is trying to model tax payments in the future.

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.

Not in spreadsheets, but there's fun to be had here in Haskell-land: https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Ta...

I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh

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

Slightly-related CLI I created: https://github.com/whyboris/mortgage-and-investments

Helped me compare different approaches to balancing paying off the mortgage vs investing.

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

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?

Are they monthly payments?

Yes

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.

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)

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

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

Not quite what you want but try this anyway. "You suck at Excel" by Joel Spolsky

https://www.youtube.com/watch?v=0nbkaYsR94c

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

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

numpy-financial does: https://github.com/numpy/numpy-financial/blob/master/numpy_f...

Reply on news.ycombinator.com