Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

Excel fixed deposit year wise interest

Shri (M.Com) (129 Points)

05 October 2013

I want to compute compound interest year wise, Can anyone help me in creating an excel sheet. I tried the net but could not find something i could use. I tried a few things but it did not work.

I have something like this:

Principal

Rate of Interest (in Percentage)

Fixed Deposit From Date:

Fixed Deposit To Date

I want yearly interest calculations.

If I have a fixed deposit for 3 years and 4 months, Then For Each year, i want interest calculations.

Also From Date can start on Any month - Say it could start in Sept, So interest calculation should be From Sept to 31st March of the current year and from 1st April of Next year to 31st March of the following year and so on.

Any Excel experts Please help me.

Reply
Follow
Share

13 Replies

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 05 October 2013

To give me quick hands on can you share me excel file on which you worked on.

Waiting for reply. You can attache file in post thru

Thanks!!

Upgrade to CAclubindia Pro. Subscribe Now!

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 05 October 2013

Are you looking something like this ? See attachments

or attach your file.

Thanks

@VaibhavJ (Believe!! Live your dreams!) (33516 Points)
Replied 05 October 2013

Attachment!

Attached File : 112266 1237135 20 years bond.xlsx downloaded: 4436 times

1 Like

Veeral Desai (Others) (39 Points)
Replied 30 April 2014

Thanks that was handy. Now I’ll be able to check my fixed deposit interest rates quite easily now with the help of this excel sheet. I was looking for something like this for so long. Now I will be able know how it works and tracks the interest at the same time.

1 Like

Narendra Pratap (Financial Adviser) (27 Points)
Replied 16 June 2014

I can suggest you to use Fixed Deposit Interest Calculator as according to me its a nice calculator to calculate FD before inversting too.

Upgrade to CAclubindia Pro. Subscribe Now!

Aman Khanna (Finance Advisior) (196 Points)
Replied 26 July 2016

Calculating interest through excel sheet can be time consuming. I suggest you to use FD interest calculator. Many banks or NBFCs provide online Fixed Deposit calculator which would helpful to determine maturity amount and earned interest.

Ashok Patnaik (6 Points)
Replied 14 August 2016

Hello Mr. Vaibhav

I saw your post today by chance while searching for a simple worksheet calculation for Quarterly Interest earned on Fixed Deposits. The amount of interest received or receivable in each quarter from all the FDs is of value for calculating Advance Tax payable.

I have posted the workbook I created. I am a simple Excel user so I created the way I understood. A note is provided from cell A11 and below in the “EntrySheet” in the attached Workbook, to understand method of working. Meticulous data entry and copying and pasting formulas is required to get correct result.

Kindly inform if you have come across a better worksheet.

Thanks

Attached File : 1668549 20160814100831 fd quarterly interest calculation.xlsx downloaded: 982 times

Ashok Patnaik (6 Points)
Replied 16 August 2016

Hello Mr. Vaibhav

In continuation to my post dated 14-08-2016, I enclose another worksheet today. It calculates quarterly interest on FDs by the Excel formula “IPMT”. It also compares the method stated in my earlier post. Note is provided at cells from K1 to Q39 in the worksheet for clarity.

Thank you very much for providing the excel file, 112266_1237135_20_years_bond.xlsx.

I used the “IPMT” formula earlier for calculating EMI and interest payable on housing loan. But it did not strike me that “IPMT” can be used for calculating interest also till I saw the above file. I am sure this is not the simplest solution yet it is good enough for calculating Advance Tax.

Thanks

Attached File : 1668549 20160816111315 ipmt quarterly fd interest calculation.xlsx downloaded: 631 times

Ashok Patnaik (6 Points)
Replied 16 August 2016

Hello Sirs,

An error was noticed in one of the formulae in the worksheet, “1668549 20160816111315 ipmt quarterly fd interest calculation.xlsx” which was uploaded at 11:15 hrs on 16-08-2016

The revised work sheet, “Revised ipmt quarterly fd interest calculation.xlsx” is uploaded at 22:25 hrs on 16-08-2016

I am really very sorry for the inadvertent error. I apologize for the inconvenience caused to the readers / users of the worksheet.

Thanks

Attached File : 1668549 20160816222800 revised ipmt quarterly fd interest calculation.xlsx downloaded: 661 times

Ashok Patnaik (6 Points)
Replied 11 September 2016

Hello friends,

An improved Workbook, FDQI Calculator.xlsx for calculation of quarterly interest on FDs is uploaded. User input cells are reduced and kept minimum in the new interest calculation worksheet.

The Workbook consists of 8 worksheets, “Readme”, “SUMMARY”, “DATASHEET”, “IPMT”, “LEAP”, “365”, “QUARTER” and “COMPARISION”.

Useful tips and instructions are provided in the worksheet, “Readme” to calculate quarterly interest.

Hope the workbook will be of use to readers.

Thanks for viewing.

Attached File : 1668549 20160911163723 fdqi calculator.xlsx downloaded: 510 times

Upgrade to CAclubindia Pro. Subscribe Now!

Shri (M.Com) (129 Points)
Replied 03 April 2017

Thanks for all the efforts. I have the following questions

1. How do i add more bank names

2. Is there a place i need to enter all the account numbers of the fds.

Thanks for your help.

Ashok Patnaik (6 Points)
Replied 09 May 2017

Cells J5 and J6 in the worksheet "IPMT" are the user input cells for name of bank and bank account number.

venkatesh soni (5 Points)
Replied 27 April 2024

Hello All, I need an Excel sheet which includes Interest Income provisions on FDR and also which includes TDS deductions so that the same can be tallied with 26AS