Ruth asks…

## Excel function – which is used for calculating interest rate for annuity ?

Hi, i got a question concern with Excel function.

I’m trying to create the mortgage calculator through Excel for my assignment. For the Normal Mortgage, with the output is Present Value of a Loan amount, i use this formula:

PVoa = PMT [(1 – (1 / (1 + i)n)) / i]

where :

PVoa = Present Value of an Ordinary **Annuity**

PMT = Amount of each payment

i = Discount Rate Per Period

n = Number of Periods

What if I have PMT, n, PVoa as the inputs and i want to calculate the interest rate (i) ? **Which** function in Excel helps me to calculate (i) ?

Thanks a lot for your suggestions.

Hi Alfonso,

Could u please be more specific ?

Say, I already got these 3 input : payment, present value of a lump sum, numbers of payments ( years * frequency), what function in Excel i should use to calculate interest ?

### Pension Forecast answers:

Hi

Use Rate Function to Calculate i

i =RATE(n, -A, P,0, type, guess)

Guess (default : 0.1)

Paul asks…

## Which of these annuities would have the greatest present value?

10-yr annuities, assuming they all have the same interest rate. **Which** of these 10-yr annuities would have the highest present value?

One that pays $500 at the beginning of every 6 months, one that pays $500 at the end of each 6 months, one that pays $1000 at the beginning of each year, or one that pays $1000 at the end of each year?

### Pension Forecast answers:

The one that pays 1000 at the beginning of each year.

The simple way to think about it:

1. It’s better to have $1000 now rather than $1000 later. Why? Because you can invest your $1000, get interest and then after one year, you have $1000 plus interest, whereas in the other scenario you only have $1000.

2. Then you can use the same argument to show why $1000 at the beginning is better than $500 twice. It’s because after six months, you will have earned interest on $1000 vs earning interest on only $500.

Robert asks…

## If annual interset rates are 10 percent. which of the following values will be the greatest?

A. the future value of an **annuity** after 4 years, if 100 dollars is deposited annually

B.the future value of a 100 dollar investment after 3 years

C. the present value of an investment that will be worth 100 dollars after 2 years

D. the present value of an **annuity** that will pay 200 dollars a year, at the end of each of the next 4 years

### Pension Forecast answers:

A. 100[(1.1)^4 – 1]/.1 = $464.10

B. 100(1.1)^3 = $133.10

C. 100/(1.1)^2 = $82.64

D. 200[(1 – (1.1)^-4)]/.1 = $633.97 = greatest

John asks…

## Assume you have a choice between 2 annuity contracts, Contract A pays $5000 per yr for 5 yrs starting 1 yr?

from today. Contract B pays $5000 per yr for 5 yrs starting today, discount rate is 6%. **Which** **annuity** contract would you choose for retirement and why??

### Pension Forecast answers:

B. Earn the $ first. Worth it

Chris asks…

## Find the value of an annuity math problem?

Find the value of an **annuity** in **which** $1,100 is deposited at the end of each year for 5 years, at an interest rate of 11.5% compounded annually.

### Pension Forecast answers:

FV = PMT [ (1+i)^n -1] / i

where FV = future value

PMT = payment = $1,100

n=5 number of years

i= 0.115 interest rate

FV =1,100 [ (1.115)^5 -1 ] / 0.115

$6,919.03

Powered by Yahoo! Answers