Calculating Rounddown Future Value

[pociners]

Hi Everyone,

I'd like to ask a question about a very simple excel problem but I don't know how to solve it, haha.
I have a sequence formula like this:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]1000[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A1*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A2*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A3*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A4*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A5*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Well, my question is there any single formula to solve this? Like calculating future value in investment, which the equation is just simply put this formula =$A$1*(1+$B$1)^(ROW(A2)-1) or in mathematic equation is :

​

where,

• P is the initial amount invested;
• r is the annual interest rate (as a decimal);
• n is the number of periods over which the investment is made.

Thanks for your help guys..
Budyono from Indonesia

 

[JoeMo]

Hi Everyone,

I'd like to ask a question about a very simple excel problem but I don't know how to solve it, haha.
I have a sequence formula like this:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]1000[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A1*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A2*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A3*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A4*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=ROUNDDOWN(A5*(1+$B$1),0)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Well, my question is there any single formula to solve this? Like calculating future value in investment, which the equation is just simply put this formula =$A$1*(1+$B$1)^(ROW(A2)-1) or in mathematic equation is :

​

where,

• P is the initial amount invested;
• r is the annual interest rate (as a decimal);
• n is the number of periods over which the investment is made.

Thanks for your help guys..
Budyono from Indonesia

If I understand your question correctly, have you looked at the worksheet function FV in Excel Help?

FV(rate,nper,pmt,[pv],[type])

 

[joeu2004]

AFAIK, there is no such formula, because the calculation of each term depends on the compounded calculations of the previous term (in a way that we can express in a mathematical series).

(You might get lucky with a formula that works, by coincidence, for the particular example. But I doubt that it would work with all examples.)

Of course, FV(B1,5,0,-A1) is a misdirection. Just take a moment to test it. (Klunk!)

FV(B1,5,0,-A1) is equivalent to propagating A1*(1+$B$1) down the column, not ROUNDDOWN(A1*(1+$B$1),0) or even ROUNDDOWN(A1*(1+$B$1),2).

Even FV(B1,5,0,-A1) in 1276.2815625. So ROUNDDOWN(FV(B1,5,0,-A1),0) results 1276.

In contrast, propagating ROUNDDOWN(A1*(1+$B$1),0) results in 1274.

 

[pociners]

If I understand your question correctly, have you looked at the worksheet function FV in Excel Help?

FV(rate,nper,pmt,[pv],[type])

Dear JoeMo,

Thanks for your reply, but that's not my point. The details is already explained by joeu2004 below.

AFAIK, there is no such formula, because the calculation of each term depends on the compounded calculations of the previous term (in a way that we can express in a mathematical series).

(You might get lucky with a formula that works, by coincidence, for the particular example. But I doubt that it would work with all examples.)

Of course, FV(B1,5,0,-A1) is a misdirection. Just take a moment to test it. (Klunk!)

FV(B1,5,0,-A1) is equivalent to propagating A1*(1+$B$1) down the column, not ROUNDDOWN(A1*(1+$B$1),0) or even ROUNDDOWN(A1*(1+$B$1),2).

Even FV(B1,5,0,-A1) in 1276.2815625. So ROUNDDOWN(FV(B1,5,0,-A1),0) results 1276.

In contrast, propagating ROUNDDOWN(A1*(1+$B$1),0) results in 1274.

Dear joeu2004,

thanks for your reply and detail explanation to joemo.
however, I'm still hoping that my problem has a solution.

Thanks,
Budyono from Indonesia

 

[joeu2004]

I believe the only solution is your own UDF (VBA function), to wit:

Function fvRounddown(r As Double, n As Double, p As Double, _
    v As Double, Optional dp As Long = 0) As Double
Dim wf As Variant, t As Double
Set wf = WorksheetFunction
t = -v
If n <> Int(n) Then t = t * (1 + r) ^ (n - Int(n)): n = Int(n)
For n = n To 1 Step -1
    t = wf.RoundDown(t * (1 + r) - p, dp)
Next
fvRounddown = t
End Function

Just as you might call FV(B1,5,0,-A1), you would call fvRounddown(B1,5,0,-A1) to round down to zero decimal places.

Caveats:

1. There is a difference when "n" is not an integer and dp=15. I believe the difference is unavoidable because humans count in integer units (1, 2, etc), whereas the mathematical formula that Excel FV uses efffectively counts in "real" steps (1.234, 2.234, etc).

2. For large integer "n", there might be a difference even with dp=15 (and PV>=1), because Excel FV uses the full binary precision, which is usually equivalent to more than 15 significant digits. However, the difference should be relatively small. For example, the result for fvRounddown(5%,360,0,-1000,15) is 0.0007 more than FV(5%,360,0,-1000) relative to about 42,476,396,408.

 

[joeu2004]

Errata....

There is a difference when "n" is not an integer and dp=15. [....] the mathematical formula that Excel FV uses efffectively counts in "real" steps (1.234, 2.234, etc).

Even that does not correctly reflect "real" counting. I don't believe humans are capable of fathoming "real" counting.

If it were as simple as 1.234, 2.234, etc, we could tweak the VBA algorithm. But counting 1.234, 2.234, etc is still counting in integer units (+1 each step). It does not result in 5.234(!) equal steps.