Can you help me decipher step by step using the high lighted formula - Microsoft Community

can me decipher step step using high lighted formula how 12.23 calculated.

ps.... wrote:

patricia wrote:

that's tried do.  not understand different steps telling me.  can me that.

[.....]

the value -12.23 in f13 comes part of formula highlighted in red, namely:

=if(d13<i11, b13*(1+i7)^((j11-i11)/365) - b13, if(and(d13>i11,d13<j11), b13 * (1+i7)^((j11-d13)/365) - b13, 0))

i focused on step-by-step evaluation because second question. perhaps original question (again, not very clear) is: highlighted expression calculating , how?

as original image shows, expression calculates "total interest" @ annual rate in i7 "gas revenue" in b13.

more specifically, calculates total interest period between 11/29/2016 (d13) , 8/25/2017 (j11).

i7 annual rate.  j11-d13 days between 2 dates.  (j11-d13)/365 number of years.  and

(1+i7)^((j11-d13)/365) effective interest rate compounded many years.

so b13*(1+i7)^((j11-d13)/365) b13 plus compounded interest. subtracting b13 results in total compounded interest period.

fyi, if d13 (11/29/2016) had been less i11 (1/1/1920 !!), value-if-true expression (not highlighted) calculate total compounded interest in similar manner period between d13 , 1/1/1920 (!).

and if d13 (11/29/2016) equal i11 (1/1/1920) or greater or equal j11 (8/25/2017), formula returns zero.

Office / Excel / Windows 8 / Office 2016