Giday UK.
'A' contains a month & year, ie: Nov 2001
'B' contains a random value, ie: 320,000 (Three hundred & twenty thousand,)
I require 'C' to recognize the year in 'A' and ascertain how many years until January 2010. (Using the above example would be 9 years). Then multiply the value in 'B' by the number of years, compounding at 7% per year. Using the example the end value in 'C' will be 588,306.91
I will be happy for the formula to identify the year as a whole but even happier if it can identify the month as a decimal, ie: 1 month = .83 Therefore the above example would mean there are 8.083 years required for the calculation.
Cheers, ScottyT
Replies (9)
Please login or register to join the discussion.
Is it always...
......7% and to Jan 2010 or would these need to be variable to?
Regards
Elliott
If the answer is no, they are fixed, then your answer is....
=B1*(1+0.07)^((40179-A1)/365)
Which equals = £556,278
Regards
Elliott
www.recenseo.co.uk
It's the format of you cell
Scotty
You have the cell set to a "Date" format, rather than a "Number" or "Currency". If you change this, you can see the answer you are after.
Regards
Elliott
Format
Reformat Column F. You have the column formatted as a Date format - change it to a numeric format. e.g. Format, cells, Number tab, Number (there are various other options and settings to get it to look the way you want it to).
Elliott has beaten me to it!
Use the analysis toolpak
While the formula given works approximately, I prefer to use the date tools in the analysis toolpak.
e.g. (1 + rate) ^ yearfrac(date1, date2)
The options of yearfrac can be used to ensure that leap years are calculated correctly or even monthly compounding rather than daily.
Cheers, Kerry.