Interest earned formula

[jklist]

Hello friends,

Need some expert advice from you all.

I have a spreadsheet which has a record of CD which my manager is planning
for the company and need to calculate the total value of each as well as
cumulative values.

I do not know how the banks calculate interest for cd and would like your
guidance on the same.

The coloums are as follows :

A B C D
E F
Sr.no--Principal amt --- Interest --- Years ---- End value --- Commulative
total
1 100,000 5.35 1
2 35,000 5.00 .6
3.
4.
5.

Please advice on the correct formula to be used.

Thanks
JKL

 

[Guest]

Take a look at:

http://office.microsoft.com/en-us/templates/TC010525001033.aspx?CategoryID=CT011815531033

or other templates in the same site.

 

[joeu2004]

I have a spreadsheet which has a record of CD which my
manager is planning for the company and need to calculate
the total value of each as well as cumulative values.
I do not know how the banks calculate interest for cd and
would like your guidance on the same.

Why not ask the bank(s) at which you have the CD accounts
what the value will be at maturity? They are your best source
of that information, if you need it for business purposes.

The coloums are as follows :
A B C D
E F
Sr.no--Principal amt --- Interest --- Years ---- End value --- Commulative
total
1 100,000 5.35 1
2 35,000 5.00 .6
[... etc ...]

First, you need to tell us whether the stated interest is the
nominal interest rate or the APY, which is the effective
compounded interest rate. Often it is the nominal interest
rate.

Second, you need to indicate the terms of the CD,
specifically whether interest is paid only at maturity or
if it is paid periodically throughout the life of the CD. The
latter reduces the effect of compounding. Usually, it is paid
only at maturity. Also, you need to tell us the frequency of
compounding interest, if the stated interest rate is not the
APY. Most often, interest is compounded daily; but
sometimes that is not the case.

All that said, assuming that "Interest" is the nominal rate
and that interest compounds daily and that interest is paid
only at maturity, the formula for "End Value" might be
(assuming Sr No 1 is row 2):

=fv(C2/365, 365*D2, 0, -B2)

Of course, the "Cumulative Amount" is simply:

For Sr No 1: =E2
For Sr No 2 et seq: =F2+E3

When you copy these formulaw down their respective columns,
the relative cell references will be updated appropriately.

Note: This is really just an approximation -- albeit a very close
approximation. The actual "end value" will depend on the
difference between the date of maturity and the date of deposit,
which you can only approximate with your "Years" column.

 

[jklist via OfficeKB.com]

Hello Gary,

The CD switch template is good especially if you swith. I was mainly looking
for the formula which can have multiple uses.

Thanks anyway.

Take a look at:

http://office.microsoft.com/en-us/templates/TC010525001033.aspx?CategoryID=CT011815531033

or other templates in the same site.

Hello friends,

[quoted text clipped - 23 lines]

Thanks
JKL

 

[jklist via OfficeKB.com]

Thanks a lot.

The FV formula works. Altthough I don't understand the 365*42 part.

Regards

I have a spreadsheet which has a record of CD which my
manager is planning for the company and need to calculate
the total value of each as well as cumulative values.
I do not know how the banks calculate interest for cd and
would like your guidance on the same.

Why not ask the bank(s) at which you have the CD accounts
what the value will be at maturity? They are your best source
of that information, if you need it for business purposes.

The coloums are as follows :
A B C D

[quoted text clipped - 4 lines]

2 35,000 5.00 .6
[... etc ...]

First, you need to tell us whether the stated interest is the
nominal interest rate or the APY, which is the effective
compounded interest rate. Often it is the nominal interest
rate.

Second, you need to indicate the terms of the CD,
specifically whether interest is paid only at maturity or
if it is paid periodically throughout the life of the CD. The
latter reduces the effect of compounding. Usually, it is paid
only at maturity. Also, you need to tell us the frequency of
compounding interest, if the stated interest rate is not the
APY. Most often, interest is compounded daily; but
sometimes that is not the case.

All that said, assuming that "Interest" is the nominal rate
and that interest compounds daily and that interest is paid
only at maturity, the formula for "End Value" might be
(assuming Sr No 1 is row 2):

=fv(C2/365, 365*D2, 0, -B2)

Of course, the "Cumulative Amount" is simply:

For Sr No 1: =E2
For Sr No 2 et seq: =F2+E3

When you copy these formulaw down their respective columns,
the relative cell references will be updated appropriately.

Note: This is really just an approximation -- albeit a very close
approximation. The actual "end value" will depend on the
difference between the date of maturity and the date of deposit,
which you can only approximate with your "Years" column.

 

[joeu2004]

The FV formula works. Altthough I don't understand
the 365*42 part.

I wrote "*D2", not "*42". In the table you posted previously,
column D contained the term of the CD in years. I said that
I assume that Sr No 1 (i.e. the first CD) is in row 2. Ergo, D2
is the term in years of the first CD. As you copy the formula
down, the relative reference will change to D3, D4, etc,
always representing the term in years for the corresponding
CD.

HTH.