Month: October 2014

I stay in the United States but I’m originally from India. I have a few fixed deposit accounts in India for which I need to calculate the interest in terms of US Dollars (as IRS taxes worldwide foreign income for US residents) and Indian rupees (so I can get a refund of the taxes in India – or not pay taxes for NRE accounts as interest is tax free).

Tracking this is difficult as my bank in India doesn’t provide a monthly/annual interest income chart, and even if it did, it would be difficult to track since the tax year in US is Jan-Dec while in India is Apr-Mar. Another complexity is that there are two kinds of fixed deposit accounts RIP (aka reinvestment accounts – where the interest is compounded quarterly but paid at the end of the term) and FD (aka fixed deposit – where the interest is deposited to another account quarterly). Either account type requires you pay the interest on an annual basis which is easy to calculate for FDs but not so much for RIPs.

So I created a spread sheet that I use to track the interest income so I don’t have to back track all this each April when taxes are due in US.

• It supports FDs (quarterly compounding and deposit) and RIPs (quarterly compounding, deposit on maturity).
• Once you setup the FDs, it will highlight entries that need an update (update meaning, you need to update the interest earned for that entry). The interest for that term is shown in the last column (FDs will have same interest, RIP interest will change every term). Once you’ve updated the ‘Last updated’ column, the ‘next update’ column will point to the minimum of (FD expiration, or the next quarterly term) so you know when this entry will next need to be updated.
• Two columns per year where you update interest (Jan-Mar) and (Apr-Dec). When calculating Indian taxes for say 2013, I’ll use 2013 Apr-Dec + 2014 Jan-Mar. To determine foreign interest so I can pay taxes in US, I’ll use 2013 Jan-March and 2013 Apr-Dec.
• Doesn’t handle non-month terms (e.g.: 1 year and 5 days, It will just show four quarters, but you can figure out the interest for the rest of the 5 days based on the maturity amount you might have been told when you created the FD).
• The total amount (cell I4) will look at only active FDs and skip matured FDs so you know how much money is invested.
• Just make sure to follow the help in the first sheet. 🙂 There are a lot of formulae and the rows are different for FD/RIP/Savings bank interest so you don’t want to overwrite the wrong cell.
• Disclaimer: By using this spreadsheet, you agree to not hold me liable for any calculation mistake made by the spreadsheet or you. I use this spreadsheet myself so it has gone through more than a few iterations of fixes or feature additions and I’m certain all calculations are correct but this basically absolves me from any mistakes that happen

So, without further ado, here’s the link to the spread sheet: http://bit.ly/amalani_fdcalc. To use this, click the link and make a copy of the spread sheet in your own Google account. If you’d like to be notified when I make a change to the spread sheet, please fill this form. http://bit.ly/amalani_fdcalcnotify.

Thanks, hope this makes your life easier 🙂 Let me know how you like it or if you want additional features.