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

111–120 of 123 posts

Re: Spreadsheet formulas for personal finance

#111

Post by Aeolun »

sokoloff wrote: post #99
Aeolun wrote: post #90

Earlier quoted context omitted.

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

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.

Re: Spreadsheet formulas for personal finance

#112

Post by scubakid »

gizdan wrote: post #108
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…

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

Re: Spreadsheet formulas for personal finance

#113

Post by scubakid »

marvin wrote: post #109
martian42 wrote: post #105

Earlier quoted context omitted.

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

Re: Spreadsheet formulas for personal finance

#114

Post by scubakid »

yarky wrote: post #97
scubakid wrote: post #86

Earlier quoted context omitted.

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…

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.

Re: Spreadsheet formulas for personal finance

#115

Post by Terretta »

the_fire_friar wrote: post #74
phonon wrote: post #60

Earlier quoted context omitted.

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.

Re: Spreadsheet formulas for personal finance

#116

Post by bouk »

nybble41 wrote: post #75

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

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

Re: Spreadsheet formulas for personal finance

#117

Post by marianov »

narush wrote: post #53
marianov wrote: post #51

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

Re: Spreadsheet formulas for personal finance

#118

Post by SeaWhales1000 »

xorfish wrote: post #25
imsd wrote: post #4

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

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

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.

Re: Spreadsheet formulas for personal finance

#119

Post by asdff »

jonshariat wrote: post #33
asdff wrote: post #28

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

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

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.

Re: Spreadsheet formulas for personal finance

#120

Post by yarky »

scubakid wrote: post #114
yarky wrote: post #97

Earlier quoted context omitted.

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

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

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

Post reply on HN