Creating a custom formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

axr0284 - 10 Feb 2008 04:12 GMT

Hi,
I am a newbie at excel so I would like to know How to do the following.
Cell A1 would contain 1 date A
Cell A2 would contain a second date B
Cell A3 would contain a number X
Cell A4 would contain a number Y
I would like cell A5 to contain a formula that will perform the following
recursively:
Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X

so if n = 1 to 20, it would iterate 20 times first using X and then the that
result in the next iteration and so on until n=28. Then it would display the
final result in cell A5.

I have not been able to figure out how to do this. Thanks for any help,
Amish

Reply to this Message

Dana DeLouis - 10 Feb 2008 04:59 GMT

> Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X

Hi. You didn't quite define your F( ) function, but if I understand it
correctly...

= (x*((y + 1)^(-A + B + 1) - y - 1))/y

Signature

HTH :>)
Dana DeLouis
"To understand recursion, one must first understand recursion."

> Hi,
> I am a newbie at excel so I would like to know How to do the following.
[quoted text clipped - 14 lines]
> I have not been able to figure out how to do this. Thanks for any help,
> Amish

Reply to this Message

Dana DeLouis - 10 Feb 2008 05:41 GMT

Oh wait. When you say for n=1 to (b-a), the first term is actually
f(n-1) -> f(0).
See if this is better. If z is the number of terms (ie b-a) then perhaps:
(x*((y + 1)^z - 1))/y

Signature

HTH :>)
Dana DeLouis

>> Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X
>
[quoted text clipped - 21 lines]
>> I have not been able to figure out how to do this. Thanks for any help,
>> Amish

Reply to this Message

axr0284 - 10 Feb 2008 06:37 GMT

No,
this does not compute what I need.

> Oh wait. When you say for n=1 to (b-a), the first term is actually
> f(n-1) -> f(0).
[quoted text clipped - 26 lines]
> >> I have not been able to figure out how to do this. Thanks for any help,
> >> Amish

Reply to this Message

Dana DeLouis - 10 Feb 2008 07:05 GMT

> No,
> this does not compute what I need.

Can you give a simple example, and the first few terms of the sequence.

Signature

Dana DeLouis

> No,
> this does not compute what I need.
[quoted text clipped - 35 lines]
>> >> help,
>> >> Amish

Reply to this Message

axr0284 - 10 Feb 2008 15:34 GMT

Here you go. I would use it to calculate my loan interest every day.
Daily interest percentage: 0.02 (Y)
Starting loan amount 7695 (X)
Starting day 01/11/08 (A)
ending date 01/16/08 (B)

In excel, if I do compute it manually it comes out like this
Amount Date
7695 01/11/08
7696.598684 01/12/08
7698.197701 01/13/08
7699.797049 01/14/08
7701.39673 01/15/08
7702.996743 01/16/08

I would like to automate this process so that i don't need to calculate each
date's amount to obtain the next one. I would put in the start and end data
and excel would calculate what I owe on the end date. I hope it's clearer now.

> > No,
> > this does not compute what I need.
[quoted text clipped - 40 lines]
> >> >> help,
> >> >> Amish

Reply to this Message

Dana DeLouis - 10 Feb 2008 16:39 GMT

>> >> >> Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X

>> > this does not compute what I need.

Hi. The sum of the first 6 terms is:

=SUM(A2:A7) ->
46,193.98691

When I enter the equation:

Sub Demo()
Dim x, y, z

x = 7695
y = 0.000207756229861
z = 6 ' (ie b-a)
Debug.Print (x * ((y + 1) ^ z - 1)) / y
End Sub

It returns the same amount.

46193.98691

The value of y is probably not exact.
=7695*(1+0.02%) does not exactly match your next value.

In your table, if the next term was actually
= 7695 * (1 + .02%) = 7696.5390
and copied down, then using

y=.02/100 would give the same amount.

46,193.09116

Signature

HTH
Dana DeLouis

> Here you go. I would use it to calculate my loan interest every day.
> Daily interest percentage: 0.02 (Y)
[quoted text clipped - 63 lines]
>> >> >> help,
>> >> >> Amish

Reply to this Message

Dana DeLouis - 10 Feb 2008 17:06 GMT

> and excel would calculate what I owe on the end date.

>> 7702.996743 01/16/08

Sounds like you no longer want SUM

The loan at time 0 is 7695.

Is this what you are looking for?
Your 5th payment is:

=FV(0.02%,5,0,-7695)

$7,702.70

Maybe??

Signature

HTH
Dana DeLouis

>>> >> >> Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X
>
[quoted text clipped - 99 lines]
>>> >> >> help,
>>> >> >> Amish

Reply to this Message

axr0284 - 11 Feb 2008 12:25 GMT

Sorry,
for some reason, when I ran your formula, the numbers did not come out
right but now i realize i made the mistake. Yeah eventually I would like to
calculate the accrued interest on the loan daily as well as the loan amount
and a bunch of different stuff. I'll check out the FV function also. Did not
know it existed. Thanks for all the help. I really appreciate it.
Amish

> > and excel would calculate what I owe on the end date.
>
[quoted text clipped - 116 lines]
> >>> >> >> help,
> >>> >> >> Amish

Reply to this Message