6

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.)

Improve this question
2

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

Improve this answer
2
  • I'm surprised to hear Excel doesn't have a built-in function for this. Thanks for the tip about Goal Seek, didn't know about that one yet! – Rinzwind Sep 24 '12 at 19:18
  • Excel has function where the payments need to be periodic, it cannot take dates in ... ie partial years etc – Dheer Sep 25 '12 at 2:52
7

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.

Improve this answer
0

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.

Improve this answer
4
  • Would the downvoters mind leaving reasons why the downvote ? And why isn't this considered an answer or part of an answer. The accepted answer has a reference to what I had answered. – DumbCoder Sep 25 '12 at 7:27
  • I didn't vote, but I think you could quote the referenced part and cite it rather than send people to a site with info that might change or disappear. – MrChrister Oct 17 '12 at 5:36
  • @MrChrister - The link is for the MS website, and I seriously doubt if the information will be removed while people are still using the product. – DumbCoder Oct 17 '12 at 7:49
  • I think MS changes their links more than most with each redesign. They try to redirect inbound links but it doesn't always work. I have personally experienced that following MSDN links from super old blogs. Just an opinion. – MrChrister Oct 17 '12 at 16:45
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.