Calculating monthly values for a certificate of deposit.

[thehed]

I undestand the use of the FV formulas in Excel to calculate Future
Value of an investment. I am looking for a way to calculate and
display the amounts of a CD on a monthly basis, sort of like an
amortization schedule for a mortgage.

Let's say the interest is paid monthly I would like to enter the
Initial amount, interest rate, term, etc. and have a chart showing
value each month. I can probably do it manually, for each month. I
was wondering if there is a formula, or easier, way to do it.

Thanks for your help.

 

[JoeU2004]

Let's say the interest is paid monthly I would like to enter the
Initial amount, interest rate, term, etc. and have a chart showing
value each month.

Do you just need the formulas, or do you also need help with the "chart"? I
am not sure if you really meant "chart" in Excel parlance -- i.e. a graph --
or if you meant to say "table".

The calculation of the periodic (monthly) rate depends on the laws of your
jurisdiction as well as on the terms of the CD.

In the US, the periodic rate is the annual interest rate divided by
compounding frequency. And in the US, some bank CDs compound daily even
though they are paid to the account monthly or at maturity.

Even the maturity date of the CD varies from bank to bank in the US. I have
had a 9-month CD that ran from, say, 1/15/2008 to 10/15/2008; and I have had
a "9-month" CD that ran from 1/15/2008 to 10/11/2008 (270 days).

Consider the simple case: compounded and paid monthly. In that case, here
is a bare-bones implementation of a monthly schedule of account.

A1: initial amount
A2: annual interest rate, entered in the form 1.23%
A3: term, in months
B2: monthly interest rate: =A2/12

D4: date that the CD account is opened
E4: initial amount: =A1

C5: period number: =if(C4<$A$3, C4+1, "")
D5: period date: =if(C5="", "", EDATE($D$4,C5))
E5: monthly balance: =if(C5="", "", E4*(1+$B$2))

Format E5 as Number with 2 decimal places, or some numeric format with 2
decimal places.

Copy C5:E5 down for at least A3 rows. But the formulas are designed to
permit a template of, say, 60 rows (5 years).


If interest compounds daily, but it is paid monthly, the only changes are:

B2: daily interest rate: =A2/365

E5: monthly balance: =if(C5="", "", FV(D5-D4,$B$2,0,-E4))


Programming notes:

1. In E5, you could replace E4*(1+$B$2) with FV($B$2,C5,0,-$A$1).

2. In E5, ou could replace FV(D5-D4,$B$2,0,-E4) with E4*(1+$B$2)^(D5-D4) or
with FV($B$2,D5-$D$3,0,-$A$1).

3. When compounding daily, US banks (et al.) may choose A2/366 instead of
A2/365 in leap years. In that case, instead of $B$2 in E5, use
$A$2/(365+(DAY(DATE(YEAR(D5),3,0))=29)).


Does that answer your question?


----- original message -----

 

[Fred Smith]

If interest is paid monthly, then the value of the CD doesn't change on a
monthly basis.

If you mean interest is compounded monthly, then just use the FV function,
as in
=FV(IntRate/12,n,0,CDAmount)
Simply build a table where n increases from 1 to the term of the CD.

If you want to build an accumulation table, monthly interest will be:
=PrevBalance*IntRate/12
Simply build a table where you add that to the starting amount of the CD.

Note that these calculations will almost never match the bank's, because of
they may use a different compounding period. But you'll be within pennies.

Regards,
Fred

 

[thehed]

I think I need to ask my question more clearly.

I track my investments in Excel. I have a workbook where each
worksheet is a separate investment. The first worksheet is a summary
of the others, # of shares, Very simple average cost basis, percentage
increase(decrease). The summary worksheet pulls the security price
from the web.

I would like to put the CD on the summary sheet. I would like it to
show the "value" of the CD as of the date I am looking at it. So if
I'm looking at the summary worksheet 6 months and 1 week from the CD
purchase, the summary page will show the initial amount of the CD plus
the 6 months accrued interest.

e.g.

DATE
8/8/9

VALUE
$1001.21

Where the date is auto filled in using the proper command (=today
()?).

Maybe I could use the FV formula with the interest calculated for an
equivalent daily rate and then use the starting date and =today() to
find difference in days from start to present and plug those number of
days into FV formula.

I do NOT need this to be EXACT...just a ballpark amount for the
present value of the CD.

I will try the above solutions...I'm working nights and my brain isn't
as sharp as it needs to be.

Thanks for the help.

 

[JoeU2004]

I would like to put the CD on the summary sheet. I would like it to
show the "value" of the CD as of the date I am looking at it. So if
I'm looking at the summary worksheet 6 months and 1 week from the CD
purchase, the summary page will show the initial amount of the CD plus
the 6 months accrued interest.

Okay. That is very different from something "sort of like an amortization
schedule for a mortgage" that you mentioned in your original posting.

But because the terms of CDs vary widely, I would put the "value to date"
calculation on the CD worksheet, and simply reference the calculated value
on the summary page. The CD worksheet can get the summary date by
referencing the date cell on the summary worksheet.

Maybe I could use the FV formula with the interest calculated for an
equivalent daily rate and then use the starting date and =today() to
find difference in days from start to present and plug those number of
days into FV formula.

I do NOT need this to be EXACT...just a ballpark amount for the
present value of the CD.

Yes, you could do that. It would be simply:

=FV(annualRate/365, currentDate - startDate, 0, -principal)

To give you some idea of how big the "ballpark", consider a $10,000 5-year
CD at 5% compounded monthly, and currentDate is a month before maturity.
Your ballpark calculation would be effectively:

=FV(5%/365, 365*5 - 30, 0, -10000) [$12,787.38]

The actual value would be effectively:

=FV(5%/12, 12*5 - 1, 0, -10000) [$12,780.34]

Less than 0.1% error. I would agree that's a good ballpark figure.


----- original message -----

 

[Fred Smith]

It's a simply future value calculation. The only complication would be the
compounding period, which I doubt is monthly -- it's more likely annual. If
so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Regards,
Fred

 

[JoeU2004]

The only complication would be the compounding period,
which I doubt is monthly -- it's more likely annual.

Well, perhaps that depends on the country of origin, as well as the
institution.

In the US, Wells Fargo Bank offers 3, 6, 10, 13, 18 and 25-month CDs and a
1-yr CD, all of which compound daily. Scwhab Brokerage offers CDs ranging
from 1 month to 10 years. The 1 and 3-month CDs pay interest at maturity;
of the remaining, 60 pay monthly, 26 pay semi-annually, and 12 pay at
maturity (all of which are 1-yr or under).

However, it should be noted that Schwab CDs do not normally compound
(although reinvestment might be an option for some; none in my experience),
so the APY is the same as the annual rate. And Wells Fargo specifies only
the APY (compounded rate) for its CDs.

So, if the OP has only the APY, not the annual rate (APR), and if interest
compounds at any frequency (note: that is not a valid assumption for the
majority of Schwab CDs), the daily interest rate can be approximated by
RATE(365, 0, -1, 1 + apy), where "apy" is expressed in the form 1.23%. So
the "daily" FV expression could be:

=FV(RATE(365,0,-1,1+apy), currentDate - startDate, 0, -principal)

(And of course, it would be preferrable to compute the RATE() expression one
time in a cell, which can be reference in the FV() expression.)

it's more likely annual. If so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Any presumption of compounding can result in a significant error if CD
interest is not compounded (reinvested).

For example, for a $10,000 5-year CD at 5% APY paid monthly without
compounding (reinvestment), both "daily" and Fred's (fractional) annual FV
formulas yield a value of $12,762.82 at maturity, compared to an actual
value of $12,500.

That is why I wrote: ``But because the terms of CDs vary widely, I would
put the "value to date"
calculation on the CD worksheet, and simply reference the calculated value
on the summary page.``


----- original message -----

 

[JoeU2004]

PS....

So the "daily" FV expression could be:
=FV(RATE(365,0,-1,1+apy), currentDate - startDate, 0, -principal)

Nothing wrong with that. But on second thought, I prefer Fred's formulation
as long as "intRate" is the APY. (The norminal annual rate is the APY when
compounding annually, as Fred assumed.) I just got lost in my own epiphany,
namely that the OP probably has the APY, not the nominal annual rate.

Just to clarify.... In the US (at least), the rate of return of CDs is
stated as an APY at least; also stating the nominal annual rate is optional.
Perhaps that is what Fred meant when he wrote "the compounding period
[...is...] more likely annual". In that case, I would agree.


----- original message -----

 

[JoeU2004]

Errata...

=FV(annualRate/365, currentDate - startDate, 0, -principal)

I assumed that you would know the nominal annual rate. That is probably
wrong. In the US (at least), the rate of return is stated as an APY
(compounded annual rate); also stating the nominal annual rate is optional.

See Fred's response for the best way to use the APY, assuming that interest
is compounded in the first place.


----- original message -----

I would like to put the CD on the summary sheet. I would like it to
show the "value" of the CD as of the date I am looking at it. So if
I'm looking at the summary worksheet 6 months and 1 week from the CD
purchase, the summary page will show the initial amount of the CD plus
the 6 months accrued interest.

Okay. That is very different from something "sort of like an amortization
schedule for a mortgage" that you mentioned in your original posting.

But because the terms of CDs vary widely, I would put the "value to date"
calculation on the CD worksheet, and simply reference the calculated value
on the summary page. The CD worksheet can get the summary date by
referencing the date cell on the summary worksheet.

Maybe I could use the FV formula with the interest calculated for an
equivalent daily rate and then use the starting date and =today() to
find difference in days from start to present and plug those number of
days into FV formula.

I do NOT need this to be EXACT...just a ballpark amount for the
present value of the CD.

Yes, you could do that. It would be simply:

=FV(annualRate/365, currentDate - startDate, 0, -principal)

To give you some idea of how big the "ballpark", consider a $10,000 5-year
CD at 5% compounded monthly, and currentDate is a month before maturity.
Your ballpark calculation would be effectively:

=FV(5%/365, 365*5 - 30, 0, -10000) [$12,787.38]

The actual value would be effectively:

=FV(5%/12, 12*5 - 1, 0, -10000) [$12,780.34]

Less than 0.1% error. I would agree that's a good ballpark figure.


----- original message -----

I think I need to ask my question more clearly.

I track my investments in Excel. I have a workbook where each
worksheet is a separate investment. The first worksheet is a summary
of the others, # of shares, Very simple average cost basis, percentage
increase(decrease). The summary worksheet pulls the security price
from the web.

I would like to put the CD on the summary sheet. I would like it to
show the "value" of the CD as of the date I am looking at it. So if
I'm looking at the summary worksheet 6 months and 1 week from the CD
purchase, the summary page will show the initial amount of the CD plus
the 6 months accrued interest.

e.g.

DATE
8/8/9

VALUE
$1001.21

Where the date is auto filled in using the proper command (=today
()?).

Maybe I could use the FV formula with the interest calculated for an
equivalent daily rate and then use the starting date and =today() to
find difference in days from start to present and plug those number of
days into FV formula.

I do NOT need this to be EXACT...just a ballpark amount for the
present value of the CD.

I will try the above solutions...I'm working nights and my brain isn't
as sharp as it needs to be.

Thanks for the help.

 

[Fred Smith]

I agree the interest rate used needs to be the APY, or whatever term is used
in the OP's country. In Canada, virtually all CDs (GICs to us) longer than a
year are quoted at the APY. Bonds are typically quoted semi-annually, but it
is generally accompanied by the APY.

Regards,
Fred

PS....

So the "daily" FV expression could be:
=FV(RATE(365,0,-1,1+apy), currentDate - startDate, 0, -principal)

Nothing wrong with that. But on second thought, I prefer Fred's
formulation as long as "intRate" is the APY. (The norminal annual rate is
the APY when compounding annually, as Fred assumed.) I just got lost in
my own epiphany, namely that the OP probably has the APY, not the nominal
annual rate.

Just to clarify.... In the US (at least), the rate of return of CDs is
stated as an APY at least; also stating the nominal annual rate is
optional. Perhaps that is what Fred meant when he wrote "the compounding
period [...is...] more likely annual". In that case, I would agree.


----- original message -----

Well, perhaps that depends on the country of origin, as well as the
institution.

In the US, Wells Fargo Bank offers 3, 6, 10, 13, 18 and 25-month CDs and
a 1-yr CD, all of which compound daily. Scwhab Brokerage offers CDs
ranging from 1 month to 10 years. The 1 and 3-month CDs pay interest at
maturity; of the remaining, 60 pay monthly, 26 pay semi-annually, and 12
pay at maturity (all of which are 1-yr or under).

However, it should be noted that Schwab CDs do not normally compound
(although reinvestment might be an option for some; none in my
experience), so the APY is the same as the annual rate. And Wells Fargo
specifies only the APY (compounded rate) for its CDs.

So, if the OP has only the APY, not the annual rate (APR), and if
interest compounds at any frequency (note: that is not a valid
assumption for the majority of Schwab CDs), the daily interest rate can
be approximated by RATE(365, 0, -1, 1 + apy), where "apy" is expressed in
the form 1.23%. So the "daily" FV expression could be:

=FV(RATE(365,0,-1,1+apy), currentDate - startDate, 0, -principal)

(And of course, it would be preferrable to compute the RATE() expression
one time in a cell, which can be reference in the FV() expression.)



Any presumption of compounding can result in a significant error if CD
interest is not compounded (reinvested).

For example, for a $10,000 5-year CD at 5% APY paid monthly without
compounding (reinvestment), both "daily" and Fred's (fractional) annual
FV formulas yield a value of $12,762.82 at maturity, compared to an
actual value of $12,500.

That is why I wrote: ``But because the terms of CDs vary widely, I would
put the "value to date"
calculation on the CD worksheet, and simply reference the calculated
value on the summary page.``


----- original message -----

 

[thehed]

Yes, you could do that.  It would be simply:

=FV(annualRate/365, currentDate - startDate, 0, -principal)

I am using the above formula as suggested, it works fine for now.

I am working on the formula posted by JoeU2004, however I use Excel at
work and OpenOffice at home. Either the formulas don't translate or I
fat fingered the formula entry.

Thanks for the help.

 

[thehed]

It's a simply future value calculation. The only complication would be the
compounding period, which I doubt is monthly -- it's more likely annual. If
so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Regards,
Fred

Interest is compounded monthly...I double checked.

 

[JoeU2004]

=FV(annualRate/365, currentDate - startDate, 0, -principal)

I am using the above formula as suggested,
it works fine for now.

I am working on the formula posted by JoeU2004,
however [...e]ither the formulas don't translate
or I fat fingered the formula entry.

I don't understand. The above formula is (one of) the formula that I
posted.

Do you mean that you are trying to get the FV(RATE(...),...) formula to
work?

Or do you mean that you are trying to get Fred's formula to work?

If the interest rate that you have is an APY (compounded rate), which is
likely, Fred's formula is the better one to use.

Show us what you are trying that does not work, and we can help you.


----- original message -----

Yes, you could do that. It would be simply:

=FV(annualRate/365, currentDate - startDate, 0, -principal)

I am using the above formula as suggested, it works fine for now.

I am working on the formula posted by JoeU2004, however I use Excel at
work and OpenOffice at home. Either the formulas don't translate or I
fat fingered the formula entry.

Thanks for the help.

 

[JoeU2004]

Interest is compounded monthly...I double checked.

But the key question is: how is the interest rate specified? Do you have a
nominal rate, sometimes called the annual percentage rate (APR)? Or do you
have a compounded rate, usually called the annual percentage yield (APY)?

If you have an APY, Fred's formula should be adequate, regardless of the
compounding frequency.

PS: But instead of TODAY(), I would put the date into a cell. Usually, I
do not want the date to change every time I open the workbook, but only on
particular days, e.g. the first or last day of the month.


----- original message -----

It's a simply future value calculation. The only complication would be the
compounding period, which I doubt is monthly -- it's more likely annual.
If
so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Regards,
Fred

Interest is compounded monthly...I double checked.