Calculate value of account accruing interest and daily expenses

[cfeigs]

I am trying to figure out a formula for the value of an account after a period of time, which is accruing compound interest, and has a weekly withdrawal W.

I have the compound interest formula is as follows:
A = P*(1 + r/n)^(n/t)
where A is the final amount, P is initial principle balance, r is interest rate, n is number of times interest is applied per time period, and t is number of time periods elapsed.

I need to also account for weekly withdrawals from this account.

Is there a way to adapt the compound interest formula to account for this?

TYIA.

 

[jonah2.0]

Beer induced opinion follows.

I am trying to figure out a formula for the value of an account after a period of time, which is accruing compound interest, and has a weekly withdrawal W.

I have the compound interest formula is as follows:
A = P*(1 + r/n)^(n/t)
where A is the final amount, P is initial principle balance, r is interest rate, n is number of times interest is applied per time period, and t is number of time periods elapsed.

I need to also account for weekly withdrawals from this account.

Is there a way to adapt the compound interest formula to account for this?

TYIA.

Minor oversight with the compound interest formula. You need to correct that.
And yes, there is a way to adapt the compound interest formula to accommodate your situation: It's called the amortization formula. It's also known as the formula for the present value of an annuity. There are 2 kinds: One kind discounts the periodic payment from the end of the periodic interval and the other kind from the beginning.

 

[cfeigs]

This is one of the formulas I found for the present value of an annuity:

P = PMT * [1 - (1/ ( (1+r)^n) ) ] / r

where P=present value of an annuity stream, PMT=dollar amount of each annuity payment, r=discount rate or interest rate, n=number of periods in which payments will be made.

I'm unsure how to apply this formula to my problem, as follows.

I have an account with principle capital P. The account is earning interest at the rate of r, compounding n times every time period. The compound interest formula in my initial post was incorrect, and should be written as follows:

A = P*(1 + r/n)^(nt)

where A is the final amount, and t is the t is the number of time periods elapsed.

If I also have an amount W, which is deducted from the account m times every period, is it possible to use the formula for the present value of an annuity to calculate A in the above equation?

For example, if:
P = 100,000
r = 0.2
n = 365
t = 1

then:
A = 100,000*(1 + 0.2/365)^(1*365)
= 122,133

What will the balance A be if an amount W=200 is deducted from the account every week (52 times every time period) ?

TYIA

 

[BigBeachBanana]

This is one of the formulas I found for the present value of an annuity:

P = PMT * [1 - (1/ ( (1+r)^n) ) ] / r

where P=present value of an annuity stream, PMT=dollar amount of each annuity payment, r=discount rate or interest rate, n=number of periods in which payments will be made.

I'm unsure how to apply this formula to my problem, as follows.

I have an account with principle capital P. The account is earning interest at the rate of r, compounding n times every time period. The compound interest formula in my initial post was incorrect, and should be written as follows:

A = P*(1 + r/n)^(nt)

where A is the final amount, and t is the t is the number of time periods elapsed.

If I also have an amount W, which is deducted from the account m times every period, is it possible to use the formula for the present value of an annuity to calculate A in the above equation?

For example, if:
P = 100,000
r = 0.2
n = 365
t = 1

then:
A = 100,000*(1 + 0.2/365)^(1*365)
= 122,133

What will the balance A be if an amount W=200 is deducted from the account every week (52 times every time period) ?

TYIA

Are you comparing the cashflows 1 year later or at time 0?

 

[cfeigs]

Are you comparing the cashflows 1 year later or at time 0?

Basically, I've got an account with a principle of P, accruing compound interest at rate r, paid n times per time period, with deductions of W occurring m times per time period, and I want to get an estimate of A the value of the account for any given time period t which for ease say is one year.

 

[BigBeachBanana]

Basically, I've got an account with a principle of P, accruing compound interest at rate r, paid n times per time period, with deductions of W occurring m times per time period, and I want to get an estimate of A the value of the account for any given time period t which for ease say is one year.

There are 2 ways to do it. One is easier than the other depending on the scenario. Do you know how many m periods you're withdrawing? or is it indefinite. In other words, do you know how the number of weeks you're withdrawing 200 or you're drawing until the account has a balance of 0?

 

[JeffM]

First, if I understand your problem, you do not want present value at all. You need future value.

Second, you say "paid" with respect to the interest bearing account. Do you mean added to the account balance ("credited" in bank lingo abd thenceforward earning interest) or actually distributed to you in money.

Third, the way you describe the problem leaves the term "period" so general as to be worthless. The calendar is messy. Months have different numbers of days and weeks. The number of days in a year is not constant. If the interest rate is for a 30-day month, interest is credited on the last day of the calendar month, and payments are made at the start of every Friday, the formula for that is going to be complex. Bankers worry about those sorts of thing and deal with them by contract (as constrained by regulation in the US) and compute them by calculation rather than formula.

To make this tractable at all, we need to make some simplifying and clarifying assumptions. I shall assume that r is stated as an ANNUAL percentage rate and that all of this is before tax (which is not realistic for periods greater than a year in the U.S.) I shall also assume that m and n are fractions of years and that m is not less than n. For example, m might be 52 (meaning weekly payments) and n might 12 (meaning monthly crediting). I shall also assume that we are not dealing with a leap year. The resulting formula will not be exact because there are, for example, slightly more than 52 weeks in a year and because how banks compute interest on a balance that varies within a compounding period depends on contractual language and is not uniform between banks. (I know how my bank used to do it because I wrote the contractual language and set up the quality control procedure: we reduced rates to a daily basis, accrued interest daily to the millionth of a penny, computed interest on the day's ending balance without regard to accruals until the first day of the month when accrued interest from the last month was added to principal.)

[math]\text {Annual PERCENTAGE rate} = r > 0;\\ \text {Number of compounding periods per year} = n \ge 1;\\ \text {Compounding period rate} = i = \dfrac{r}{100n};\\ \text {Daily rate} = d = (1 + i)^{n/365} - 1;\\ \text {Principal } = P > 0;\\ \text {Number of withdrawals per year} = m \ge n;\\ \text {Amount of each withdrawal} = w;\ text { and}\\ \text {number of coumpounding periods} = t.[/math]
Then the APPROXIMATE formula is

[math]P(1 + d)^{365t/n)} - \dfrac{w(1 + d)^{365mt/n} - 1}{d}.[/math]
I am hoping that @BigBeachBanana will check my logic.

 

[BigBeachBanana]

First, if I understand your problem, you do not want present value at all. You need future value.

Second, you say "paid" with respect to the interest bearing account. Do you mean added to the account balance ("credited" in bank lingo abd thenceforward earning interest) or actually distributed to you in money.

Third, the way you describe the problem leaves the term "period" so general as to be worthless. The calendar is messy. Months have different numbers of days and weeks. The number of days in a year is not constant. If the interest rate is for a 30-day month, interest is credited on the last day of the calendar month, and payments are made at the start of every Friday, the formula for that is going to be complex. Bankers worry about those sorts of thing and deal with them by contract (as constrained by regulation in the US) and compute them by calculation rather than formula.

To make this tractable at all, we need to make some simplifying and clarifying assumptions. I shall assume that r is stated as an ANNUAL percentage rate and that all of this is before tax (which is not realistic for periods greater than a year in the U.S.) I shall also assume that m and n are fractions of years and that m is not less than n. For example, m might be 52 (meaning weekly payments) and n might 12 (meaning monthly crediting). I shall also assume that we are not dealing with a leap year. The resulting formula will not be exact because there are, for example, slightly more than 52 weeks in a year and because how banks compute interest on a balance that varies within a compounding period depends on contractual language and is not uniform between banks. (I know how my bank used to do it because I wrote the contractual language and set up the quality control procedure: we reduced rates to a daily basis, accrued interest daily to the millionth of a penny, computed interest on the day's ending balance without regard to accruals until the first day of the month when accrued interest from the last month was added to principal.)

[math]\text {Annual PERCENTAGE rate} = r > 0;\\ \text {Number of compounding periods per year} = n \ge 1;\\ \text {Compounding period rate} = i = \dfrac{r}{100n};\\ \text {Daily rate} = d = (1 + i)^{n/365} - 1;\\ \text {Principal } = P > 0;\\ \text {Number of withdrawals per year} = m \ge n;\\ \text {Amount of each withdrawal} = w;\ text { and}\\ \text {number of coumpounding periods} = t.[/math]
Then the APPROXIMATE formula is

[math]P(1 + d)^{365t/n)} - \dfrac{w(1 + d)^{365mt/n} - 1}{d}.[/math]
I am hoping that @BigBeachBanana will check my logic.

Hey @JeffM, the OP indicated that the number of compounding periods for the account is daily i.e.n=365. I'm not sure the need for your variable n?

 

[JeffM]

Hey @JeffM, the OP indicated that the number of compounding periods for the account is daily i.e.n=365. I'm not sure the need for your variable n?

I thought he said that n = 365 was just an example, that he wants a completely generic formula. My mistake.

I'll ask SK to delete my post.

 

[BigBeachBanana]

I thought he said that n = 365 was just an example, that he wants a completely generic formula. My mistake.

I'll ask SK to delete my post.

I think your formula just need a minor adjustment. Instead of 'hardcoding" 365, replace that with n.

 

[JeffM]

I think your formula just need a minor adjustment. Instead of 'hardcoding" 365, replace that with n.

Obviously, it is much simpler if everything is being computed in days. If that in fact is what the poster is after, I'll simplify the formula once that is confirmed.

But you are OK with the general logic? My head was beginning to swim with all the different periods apparently floating around.

 

[BigBeachBanana]

Obviously, it is much simpler if everything is being computed in days. If that in fact is what the poster is after, I'll simplify the formula once that is confirmed.

But you are OK with the general logic? My head was beginning to swim with all the different periods apparently floating around.

Yes, your logic is correct, just gotta get the compounding period and the rate down.
Given annual nomial rate r, to covert to m-thly effective rate, [imath]i^{(m)}[/imath]:
[math](1+r)^t=\left(1+\frac{i^{(m)}}{m}\right)^{mt}[/math].
[math]\text{Account Balance at time t=}P\left(1 + \frac{r}{n}\right)^{nt} - w\dfrac{(1 +\frac{r}{m} )^{mt} - 1}{r\div m}[/math]

 

[cfeigs]

There are 2 ways to do it. One is easier than the other depending on the scenario. Do you know how many m periods you're withdrawing? or is it indefinite. In other words, do you know how the number of weeks you're withdrawing 200 or you're drawing until the account has a balance of 0?

Say I'll be withdrawing $200 every two weeks. Also say for example, the principle is $100,000, the interest rate is 20%, and it's paid every 8 hours.

 

[cfeigs]

Y

I thought he said that n = 365 was just an example, that he wants a completely generic formula. My mistake.

I'll ask SK to delete my post.

You are correct that n=365 was just an example. In my actual problem, the interest is paid every 8 hours, so n=365*3. I was wanting to keep it generic as you say.

 

[cfeigs]

First, if I understand your problem, you do not want present value at all. You need future value.

Yes future value is what I'm after. Thank you.

Second, you say "paid" with respect to the interest bearing account. Do you mean added to the account balance ("credited" in bank lingo abd thenceforward earning interest) or actually distributed to you in money.

Yes the interest is credited to the account. This is an interest bearing account.

Third, the way you describe the problem leaves the term "period" so general as to be worthless. The calendar is messy. Months have different numbers of days and weeks. The number of days in a year is not constant. If the interest rate is for a 30-day month, interest is credited on the last day of the calendar month, and payments are made at the start of every Friday, the formula for that is going to be complex. Bankers worry about those sorts of thing and deal with them by contract (as constrained by regulation in the US) and compute them by calculation rather than formula.

I see what you mean about the period. In this case, it refers to an annual period, as this is what the interest rate is expressed over i.e. 20% p.a.

My understanding was that n and m can then expressed in terms of number of times per period (in this case, one year). So if interest is paid into the account every 8 hours, then n=365*3, and if withdrawals W are made every two weeks, then m=52/2.

Perhaps there is a better way to construct the period, and the expressions of n and m tho...

To make this tractable at all, we need to make some simplifying and clarifying assumptions. I shall assume that r is stated as an ANNUAL percentage rate

it is

and that all of this is before tax (which is not realistic for periods greater than a year in the U.S.) I shall also assume that m and n are fractions of years and that m is not less than n. For example, m might be 52 (meaning weekly payments) and n might 12 (meaning monthly crediting). I shall also assume that we are not dealing with a leap year. The resulting formula will not be exact because there are, for example, slightly more than 52 weeks in a year and because how banks compute interest on a balance that varies within a compounding period depends on contractual language and is not uniform between banks. (I know how my bank used to do it because I wrote the contractual language and set up the quality control procedure: we reduced rates to a daily basis, accrued interest daily to the millionth of a penny, computed interest on the day's ending balance without regard to accruals until the first day of the month when accrued interest from the last month was added to principal.)

[math]\text {Annual PERCENTAGE rate} = r > 0;\\ \text {Number of compounding periods per year} = n \ge 1;\\ \text {Compounding period rate} = i = \dfrac{r}{100n};\\ \text {Daily rate} = d = (1 + i)^{n/365} - 1;\\ \text {Principal } = P > 0;\\ \text {Number of withdrawals per year} = m \ge n;\\ \text {Amount of each withdrawal} = w;\ text { and}\\ \text {number of coumpounding periods} = t.[/math]
Then the APPROXIMATE formula is

[math]P(1 + d)^{365t/n)} - \dfrac{w(1 + d)^{365mt/n} - 1}{d}.[/math]
I am hoping that @BigBeachBanana will check my logic.

 

[JeffM]

This clarifies things a lot. First, we are not trying to deal with a practical problem that involves issue like banking and tax regulations. So we can ignore them. It is a problem in theory.

Second, and most helpful, we have a base period, namely 8 hours, which we can use as consistent unit.

Our first simplifying assumption is that there are 365.25 days per year (to account for leap years without the century adjustments.) I shall change the notation a bit. Let

[math]y = \text {The nominal PERCENTAGE rate of interest per YEAR.}[/math].

Now the first thing to do is to turn that into the effective interest rate per 8 hour period. Call that r.

[math]r = \dfrac{y}{100 * 365.25 * 8} = \dfrac{y}{292200}.[/math]
From this point on, we can ignore any consideration of years or annual rates. Our base period is 8 hours. The number of periods, we shall call t.

The number of periods between payments we shall call m. So, for example, if payments are at the end of every two weeks [math]m = 3 * 2 * 7 = 42.[/math]
Now we need to know what the yield is for these payment periods. Let’s call that pi.

[math]\pi = (1 + r)^m - 1.[/math]
Now making the assumption that t is an integer number of payment periods > 0, (that is that we do not have any payments partially due but unpaid), our formula is pretty simple. If F is the future value, P is the initial value of principal, and C are the charges

[math]F = P(1 + r)^t - \dfrac{C\{(1 + \pi)^{t/m} - 1\}}{\pi}.[/math]
Test it with a spread sheet. If you find a problem, come back and talk. It is always possible I made some silly error.

 

[cfeigs]

This clarifies things a lot. First, we are not trying to deal with a practical problem that involves issue like banking and tax regulations. So we can ignore them. It is a problem in theory.

Second, and most helpful, we have a base period, namely 8 hours, which we can use as consistent unit.

Our first simplifying assumption is that there are 365.25 days per year (to account for leap years without the century adjustments.) I shall change the notation a bit. Let

[math]y = \text {The nominal PERCENTAGE rate of interest per YEAR.}[/math].

Now the first thing to do is to turn that into the effective interest rate per 8 hour period. Call that r.

[math]r = \dfrac{y}{100 * 365.25 * 8} = \dfrac{y}{292200}.[/math]
From this point on, we can ignore any consideration of years or annual rates. Our base period is 8 hours. The number of periods, we shall call t.

The number of periods between payments we shall call m. So, for example, if payments are at the end of every two weeks [math]m = 3 * 2 * 7 = 42.[/math]
Now we need to know what the yield is for these payment periods. Let’s call that pi.

[math]\pi = (1 + r)^m - 1.[/math]
Now making the assumption that t is an integer number of payment periods > 0, (that is that we do not have any payments partially due but unpaid), our formula is pretty simple. If F is the future value, P is the initial value of principal, and C are the charges

[math]F = P(1 + r)^t - \dfrac{C\{(1 + \pi)^{t/m} - 1\}}{\pi}.[/math]
Test it with a spread sheet. If you find a problem, come back and talk. It is always possible I made some silly error.

That's great thank you!!

I found one error in r, should be:
r = y/(100*365.25*3)

 

[JeffM]

That's great thank you!!

I found one error in r, should be:
r = y/(100*365.25*3)

You are correct. Thank you.

 

[BigBeachBanana]

[math]F = P(1 + r)^t - \dfrac{C\{(1 + \pi)^{t/m} - 1\}}{\pi}.[/math]

Hey Jeff, if I recall correctly, the accumulation annuities payment, in general, is:
[math]A=\frac{(1+r)^\red{n}-1}{r}[/math]So I think it should be:
[math]F = P(1 + r)^t - \dfrac{C\{(1 + \pi)^{\red{tm}} - 1\}}{\pi}.[/math]I could be wrong because I usually don't change the basis of the period.

 

[JeffM]

@BigBeachBanana

You are of course correct on the general formula for the future value of an annuity. I did not bother to build a spread sheet to test the combined formula, but did suggest that the OP do so. So I may have got my brain scrambled on factoring in the different time periods. What follows is a plausibility argument that would need a proof, probably by induction, to make rigorous.

[imath]\pi[/imath] is defined based on a power of m and is larger than r. But when we splice our two future value formulas together, we are working with t periods. The exponent of t/m reflects that we already exponentiated by m when calculating [imath]\pi[/imath] to begin with. Exponentiating by tm would exponentiate by m twice. We are not exponentiating the same quantity so the exponentiation by m and later exponentiation by t/m does not result in canceling the effect of m. But exponentiating by m and later by tm would exaggerate the effect of m.

It was such an unusual scenario that I really did not want to spend the time on a rigorous proof. We used to have a tutor here (mon ami Denis, now sadly deceased) who always solved these problems with “bank statements.” I thought I’d leave that confirmation step to the OP. I am, however, reasonably confident that the exponents make sense.