Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interest earned formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interest earned formula
Take a look at:
http://office.microsoft.com/en-us/te...CT011815531033 or other templates in the same site. -- Gary's Student "jklist" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interest earned formula
jklist wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interest earned formula
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. Gary''s Student wrote: Take a look at: http://office.microsoft.com/en-us/te...CT011815531033 or other templates in the same site. Hello friends, [quoted text clipped - 23 lines] Thanks JKL -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200606/1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Interest earned formula
jklist via OfficeKB.com wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to calculate cumulative interest | Excel Worksheet Functions | |||
Interest Functions | Excel Worksheet Functions | |||
Interest Earned Formula | Excel Discussion (Misc queries) | |||
simple interest formula in excel | Excel Discussion (Misc queries) | |||
Interest formula on Personal Line | Excel Discussion (Misc queries) |