What Excel function to use to calculate the return or interest rate on a savings account?

Question

I'm getting confused by Excel's various different financial functions. Which one would I use to answer the following two questions and how?

• Suppose that on October 1st, 2012 I open a savings account and make a deposit of €10,000,000. The savings account pays an annual interest rate of 2.00%, deposited on the first of each year (for the interest of the preceding year). I will close the account on March 15th, 2014. What Excel function tells me what the payout on that date will be?
• Conversely, in a similar scenario, upon closing the account I want to receive a payout of €10,300,000. What Excel function tells me what the interest rate of the savings account would have to be?

(I picked a large number for the initial deposit for interest compounding effects to stand out in the calculation. It's, unfortunately for me, a hypothetical number.)

Answer 1

There is no ready made forumulae.
Put the Deposit in cell B3 as 10,000,000
Put the rate of interest in Cell B2 as 2% [Format this as Percnetage]
In Cell c3 write the formulae as =B3*B2*3/12 [Interest for 3 months till Dec]
In cell B4 write =B3+C3 [Principal as of 1st jan 13 and in cell C4 as =B4*B2 [Interest for full years]
In Cell B5 wirte = B4+C4 [new principal as of Jan 14] and in cell C5 as = B5*B2*3/12 [interest for 3 months on year 2014]
In Cell B6 write = B5+C5. This is your revised amount at the end of period. My Calculation show this as 10,319,340

If you now want to find the interest for final amount of 10,300,000 use the Goal Seek function in XLS 2003 it is under tools -> Goal Seek , Enter the values as
Set Cell: B6
To Value: 10,300,000
By Changing Cell: B2

The answer I got for the above problem is 1.87982299790449

Answer 2

http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page1

The link provided above shows you how to turn Excel into a Financial Calculator.

Your question is a little more advanced because of the specific dates involved.. but If I simplified the 1st question and used the FV function in Excel this is what i would do:

What to include in the excel cell: =FV(rate,nper,pmt,pv,type)

I would included the following:

rate = 2% fairly straightforward..

nper = 1.5 number of payments.. it's for a period of one and a half years.. Its actually 2 payments, 1 each year, compounding 1 time each year.. so you could put 2

pmt = 0 (you aren't recieving or contributing payments)

pv = -10,000,000 The present value of the investment, on a financial caluculator this would be -10,000,000 because the money is going out of your pocket..

type = leave this out

=FV(2%, 1.5, 0, -10000000) that should equal 10,301,495.04 Thats according to my financial calculator.. if you replace nper 1.5 with 2 then you get 10404000.00

You could also calculate Future Value (FV) of the first 3 months with a different interest rate (the interest rate for 3 months, instead of a year) Then you just need to do 3 future value functions, with the result of each one becoming the present value of the next (pv)

The function for your second question would be Rate(nper,pmt,pv,fv,type,guess)

You can ignore the type and guess part..

I would enter into an encel cell:

=Rate(1.5, 0, -10000000, 10300000)

this assumes that payments are once per year, and compounding is once per year:

n (number of payments) = 1.5 (1 and a half years) pmt = 0 (you are not making or recieving payments) pv = -10000000 (the present value of your investment, it's negative, because you don't have the money in your pocket) fv = 10300000 (the future value that you want)

The result in a financial calculator is 1.990131 it will round it to 2% probably.. in excel.. you'll want to make sure the format allows for decimal places to get accurate numbers..

Edit: I noticed that my version of Excel (2010) had all the proper financial calculations and instructions on how to use them. The FV function rate, would be the percentage for the year, divided by how often you pay during the year. Type is Begin or End mode. It functions identically with a financial calculator when you use it.

Answer 3

Use goal seek if you know what your goal is and then try to arrange the parameters accordingly to achieve your goal.

Go to Data tab -> What if analysis.

Answer 4

R = I ^ P

R is your return. A return of 2 means double your money.

I is (what I call) the interest ratio. It the interest rate is 10.4% then the interest ratio is 1.104. (interest rate / 100) +1

P is the number of periods that the interest rate is paid.

I = R ^ (1/P)

P = log(R) / log(I)

2 = 1.104^7

1.104= 2^(1/7)

7= log(2) / log(1.104)

The functions you need from Excel are Exponentiation, logarithms,and division.