Home inventory new for old calculation

[tonyt]

Hi
I am making a home inventory form. I have three fields in question…price paid, date purchased and years of useful life, and deprecated value which is a calculation field based on the first three fields. This gives me a falling value based on a calculation I pinched from somewhere here on the forum. i don’t write them well myself. So..
What I want to do is calculate a rising value field based on cost price and annual inflation, but on a daily basis to show what it would cost to replace an item, new for old price.
So..item cost 100, inflation at 2% p.a., cost after one year 102.
Can anyone help me please?
Cheers….

[Leo]

Is this something useful:

calculation:

[cost]/100*(100+[inflation])

Leo

[Leo]

PS.

make sure to make the inflation field a number field (no percentage sign)

[tonyt]

Hi Leo
Thanks for that. I had got somewhere near that with (cost/100)*2+cost. That assumes inflation is 2%. That would give a figure as a one off. My scenario is that lets say, for instance…

Cost = 100 on 1st January (cost field and date purchased field)
Inflation is 2%
So after one year cost to replace would be 102. However after two years the cost to replace would be: new cost to replace – 102/100 *2 + cost or.. 104.04 – I THINK! So the formula/s would have to take account of the new cost to replace after one year + 2% plus cost to replace after one year. Clear as mud? I know what I want to do but cant explain very well.

I hope the numbers sheet helps a bit and look forward to hearing from you.

Cheers…

Attachments:

You must be logged in to view attached files.

[Leo]

Yep, that is clear.

Formula would be

[cost]/100*(100+[inflation1])/100*(100+[inflation2])/100*(100+[inflation3])

could be that some () are missing, just give it a try.

Just set the percentage for the different inflationyears

[tonyt]

Hi
I haven’t tried it yet but is there a way that the calculation could auto update on a yearly basis based on the purchase date as a starting point?
I am way out of my depth here, know what I want to do but no idea how to get there!
Cheers…..

[tonyt]

Can anyone help?
Thanks, maybe not possible and if so, no problem!
Cheers….

[Brendan]

Hi Tony,

I’m not really sure but maybe you could incorporate the year number into your formula to somehow get what you’re looking for? You could pick a starting year and then subtract the current year from it to get a year number. Then multiply that by 0.02 to get a 2% inflation? So year 1 would be 1 * 0.02 = 0.02. Year 2 would be 2 * 0.02 = 0.04, and so on.

Something like this:

(YEARS(2018; NOW()) + 1) * 0.02 * [Cost]

So the first year would give you 0 because 2018 – 2018 = 0, so we add 1. Or you could just start it at 2017 instead of adding the 1. So the result is 0.02 for the first year, 0.04 for the next year, etc.

That seems to jive with your formula in one of your previous posts for a $100 item being $102 in the first year and $104 in the second year, and so on.

Thanks!

Brendan

[tonyt]

Hi
I gave up on this as I can’t write formulas but am now revisiting it! Can anyone point me in the right direction? I have tried the above with little success. What I want to do is…

Calculate a rising value field based on cost price, purchase date, current date and annual inflation, but possibly on a daily basis or annual if easier, to show what it would cost to replace an item, new for old price in 2019

So if an item was purchased in 2015 at a cost of 100 and inflation was 2%…
In 2019 the new for old figure would be based on the cost price plus four years of inflation at 2%?

The formula would have to work for ANY date prior to 2019 and for ANY date in the future. All I would have to do is amend the annual inflation figure in the formula occasionally to reflect average inflation over a number of years!

Many thanks
tonyt

[Sam Moffatt]

It should be something like this:

[Purchase Cost] * POW(1.02, YEARS([Purchase Date],NOW()))

Standard compound interest formula, 1.02 is the interest rate (2%) and Purchase Cost and Purchase Date are the two fields you have.

Edit: Re-read the OP and saw the per day specifier, I think something like this should do it:

[Purchase Cost] * POW(1 + (0.02 / 365), (DAYS([Purchase Date],NOW())))

Similar to above except DAYS instead of YEARS and divide by 365 to approximate the yearly rate into the daily rate. As always: trust but verify, do your own math to make sure it all comes to the right number.

Make sure you change your calculation field to have the number of decimal places you want and I swapped it into currency style for the number format. Make sure your result type for the calculation is number (should be the default).

[tonyt]

Hi Sam
Sorry for the delay but been away and thanks for responding. I have entered and double checked the lower of the two formulas and I get nada! How do I alter the interest rate if I need to, assuming I can get it to work? As you can see, formulas are not my thing and I wish they were!!
Hope you can help me some more.
Cheers….

Here is what I have enteredPurchase Cost*POW(1+(0.02/365),DAYS(Purchase Date
),NOW() )))

[Sam Moffatt]

You’re going to have to replace Purchase Date and Purchase cost with the correct placeholders for those equivalent fields in your database.

Can you take a quick screenshot of the formula editor as copy/paste doesn’t work properly?

One simple thing if it’s there is there shouldn’t be a ) after Purchase Date before the comma. I also had an extra open bracket ( which you don’t have which means that after NOW( should only be three ) characters.

My normal debugging technique is to delete everything and start back with something basic, in this case probably just the [Purchase Cost] or DAYS([Purchase Date], NOW()) to see what sort of response I get and then slowly built up changes around it.

[tonyt]

Hi Sam
Thanks for swift response, it works, tried and tested! So grateful. What about changing the inflation rate from say 2% to 3% or 4% if I want to in the future? I intend to run the database for many years, working on a 10 year period of average inflation which I will adjust yearly.
So:
For 2019 I will find the average inflation for 2009 – 2019 and use that figure in the formula.
For 2020 I will find the average inflation for 2010 – 2020 and use that figure in the formula.
And so on..
This is because over 10 years the figure may well rise or fall for average inflation over a number of years and I want to be able to report to my insurers the cost to replace items new for old.
Hope this all makes sense and you can help one last time.
Many thanks again
Cheers…

[Sam Moffatt]

The 0.02 number is your inflation rate, 2% in this case. Just change that constant to what ever you want it to be and refresh your record to get the updated value. If you tick the box for running only once it’ll stick. You could put an extra field in the form for the inflation rate you want to use for the record.

With scripting you could do something a little more complicated and with some join fields you might be able to do something where you have a form with records per year and the interest rate you want to set.

[Author]

Posts