Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Spreadsheet formulas for personal finance

bou.ke

Post reply on HN
Print view View original

71–80 of 123 posts

Re: Spreadsheet formulas for personal finance

#71

Post by scubakid »

enchiridion wrote: post #69
scubakid wrote: post #68

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 critica…

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.

Re: Spreadsheet formulas for personal finance

#73

Post by 9erdelta »

jplr8922 wrote: post #37

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 pr…

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.

Re: Spreadsheet formulas for personal finance

#74

Post by the_fire_friar »

phonon wrote: post #60
the_fire_friar wrote: post #15

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 earl…

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.

Re: Spreadsheet formulas for personal finance

#75

Post by nybble41 »

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

Re: Spreadsheet formulas for personal finance

#76

Post by bunabhucan »

basseq wrote: post #10

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 yea…

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.

Re: Spreadsheet formulas for personal finance

#77

Post by orzig »

drited wrote: post #65
divbzero wrote: post #59

> 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 earning…

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.

Re: Spreadsheet formulas for personal finance

#78

Post by taviso »

basseq wrote: post #31
bouk wrote: post #21

Earlier quoted context omitted.

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)

Re: Spreadsheet formulas for personal finance

#79

Post by shashank »

9erdelta wrote: post #73
jplr8922 wrote: post #37

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 pr…

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.

Re: Spreadsheet formulas for personal finance

#80

Post by gerdesj »

jplr8922 wrote: post #37

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 pr…

This is well thought of in that sphere:

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

Post reply on HN