mode=0 indicates an ordinary annuity (payments end of the month). This is the default mode and should not be given. mode=1 indicates an annuity due (payments beginning of the month)
Description
This is a simple financial function to calculate loan or investments. In case of loan, Pmt is a positive value. In case of investments, Pmt is a negative value.
Fill in the known values in the indicated order and use any variable name for the unknown. In the examples below, the unknown is indicated with it's own name but you can use any variable name.
Based on the examples from Stan Brown, Oak Road Systems.
Examples
Assume you want to set apart $50 at the end of each month for your child starting the same month as the child was born. How much will be available if the child turns 19 years old. Assume an interest rate of 5% compounded monthly.
Finance(0, FV, -50, 5/12, 18*12)
You have a $18,000 car loan at 14.25% per year for a period of 36 months. Every month you pay $620. How big is the payoff amount after 24 months?
Finance(18000, FV, 620, 14.25/12, 24)
You are buying a $250,000 house, with 10% down, on a 30-year mortgage at a fixed rate of 7.8%. What is the monthly payment?
Finance(225000, 0, Pmt, 7.8/12, 30*12)
If you loan $3500 at 6% rate and and you pay back $100 per month. How many months do you have to pay?
Finance(3500, 0, 100, 6/12, n)
In the above example, you will make payments for 38 month. How much will you pay for the last month?
Finance(3500, FV, 100, 6/12, 38)
If you deposit $100 per month over 5 years at 6% interest, how much money will you have at the end?
Finance(0, FV, -100, 6/12, 60)
You have $15,000 in a 5% savings account, which is compounded monthly. How long can you withdraw $100 a month?
Finance(15000, 0, 100, 5/12, n)
You want to purchase a 20-year annuity that will pay $500 a month. If the guaranteed interest rate is 4%, how much will the annuity cost?
Finance(PV, 0, 500, 4/12, 20*12)
On the same day every year, you put $2000 into stocks. If the market rises 8% a year, how many years will it take you to accumulate $40,000?
Finance(0, 40000, -2000, 8, n)
If you deposit $300 at the beginning of each month over 6 years at 10% interest, how much money will be in the fund at the end?
Finance(0, FV, -300, 10/12, 72, 1)
The monthly rent on an apartment is $950 payable at the beginning of the month. If the current interest is 12% compounded monthly, what single payment 12 months in advance would be equal to a year's rent.
Finance(PV, 0, 950, 12/12, 12, 1)
To obtain an amount of $3,000 ten years from now, earning 10% annually, what amount would have to be invested on a yearly basis in an annuity due form?
Finance(0, -3000, Pmt, 10, 10, 1)
The time $1,500 is needed and you know in advance that you have 10 years to obtain it. What annual interest rate would a $100 annuity due have to earn for you to achieve this goal?
Finance(0, 1500, -100, i, 10, 1)
You are doing a one time investment of $2,500. What is accumulated value after 10 years at a fixed interest rate of 5%?
Finance(2500, FV, 0, 5, 10)
You need $19,500 within 5 years from now. How much do you need to deposit today at a fixed interest rate of 3.25%?
Finance(PV, 19500, 0, 3.25, 5)
What interest rate would be needed to receive $20,000 after 20 years when you deposit $8,000 now?
