vert line

CALCRATE or @CALCRATE

Calculates the interest rate on an investment, given its present value, the amount of each payment, and the number of payment periods.

CALCRATE(pv, pmt, per)

pv the present value of the investment

pmt the amount of each payment

per the number of payment periods

Given an investment with a present value of pv, payments of pmt, and per payment periods, the CALCRATE function returns the interest rate such that pv= @PV(pmt,rate,term). See "@PV" .

Find the monthly interest rate of a 36 month car loan on $17,000 with a monthly payment of $500:

=CALCRATE(17000,500,36) = 0.31% per month or 3.75% per year.

@CTERM

Computes the number of periods required for an investment to grow to the specified Future Value with a given interest rate.

@CTERM(rate, fv, pv)

rate the interest rate

fv the desired Future Value of the investment

pv the Present Value of the investment

Given an investment with a Present Value of pv, the @CTERM function returns the number of periods required for it to grow to a Future Value of fv at an interest rate of rate.

How long will it take for a $20,000 investment to grow to $40,000 at an annual rate of 5%?

@CTERM(0.05,20000,40000) -> 14.2 years

How many months will it take for a $4,000 investment to grow to $8,000, being compounded monthly at a rate of 8%?

@CTERM(8%/12,4000,8000) -> 104 months (about 8 1/2 years)

DDBor @DDB

Computes the Double Declining Balance depreciation for an asset.

DDB(cost, salvage, life, per)

cost the original cost of the asset

salvage the salvage value of the asset

life the expected life of the asset

per the number or periods for the depreciation calculation

Given an asset that has a cost of cost, a salvage value of salvage, and a life of life periods, the DDB function will return the depreciated value of the asset after per periods.

A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 3?

year 1: =DDB(12000,2000,5,1) -> $4,800

year 2: =DDB(12000,2000,5,2) -> $2,880

year 3: =DDB(12000,2000,5,3) -> $1,728

FVor @FV

Computes the Future Value of a series of payments, given a specified interest rate. An optional present value and starting time for the first payment may be specified.

FV(rate, per, pmt[, pv[, type]])

rate the interest rate

per the number of payments

pmt the size of each payment

pv the Present Value

type the starting time of the first payment

Returns the Future Value of a stream of payments over per periods at rate interest rate. If present value pv is not specified, it is assumed to be 0. Type designates when the payments start.

If $2,000 were deposited every year into an account earning 9% annual interest, how much would the account be worth in 20 years?

=FV(9%,20,-2000) -> $102,320.24

If a $50,000 were deposited instead and accrued 9% interest from the start of the year, what would the account be worth in 20 years?

=FV(9%,20,,-50000,1) -> $ 280,220.54

IRR

Calculates the Internal Rate of Return of an investment.

IRR(guess, num1, num2, ...), IRR(guess, range1, ...)

guess any number

num any number

range any worksheet range

IRR() calculates the internal rate of return for a series of cash flow values. Each value represents either a payout (a negative value) or a payment received (positive value). The internal rate of return is the interest rate used to discount future cash flows such that the net present value of all the cash flows is zero. You may list any combination of numbers, cell addresses, and ranges as arguments.

A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return?

A

1 -180000

2 25000

3 35000

4 39000

5 40000

6 55000

The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value:

=IRR(B1,A1:A6) -> 2.3%

@IRR

Computes the Internal Rate of Return of an investment. String entries are considered zero values.

@IRR(guess, num1, num2, ...), @IRR(guess, range1, ...)

guess any number

num any number

range any range

@IRR() calculates the internal rate of return for a series of cash flow values. Each value represents either a payout (a negative value) or a payment received (positive value). The internal rate of return is the interest rate used to discount future cash flows such that the net present value of all the cash flows is zero. You may list any combination of numbers, cell addresses, and ranges as arguments.

A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return?

A

1 -180000

2 25000

3 35000

4 39000

5 40000

6 55000

The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value:

@IRR(B1,A1:A6) -> 2.3%

NPV

Computes the Net Present Value of an investment with a given stream of cash flows at a given interest rate. String values are ignored.

NPV(rate, pmt1[, pmt2[, ...]])

rate the interest rate

pmt a number or range - the payment amounts

Given an interest rate of rate and payments pmt1, pmt2, etc., the NPV function returns the Net Present Value of the stream of payments. Each payment can be a value or a range. If it is a range, each of the numbers in the range is included in the calculation. You may list any combination of numbers, cell addresses, and ranges as arguments for pmt.

An apartment building is being offered for sale for $400,000. Expected rental incomes for years 1-5 are $79,000, $85,000, $85,000, $103,000 and $120,000 and the interest rate is expected to stay close to 7%. Is this a worthwhile investment?

A

1 -400000

2 79000

3 85000

4 85000

5 103000

6 120000

To figure out the net present value of the cash in-flows:

=NPV(7%,A2:A6) -> $381,596

To figure out the value of the project:

Project Value = Initial Cost + NPV

+A1+NPV(7%,A2:A6) -> -$18,404

No, this project is not worthwhile.

@NPV

Computes the Net Present Value of an investment with a given stream of income at a given interest rate. String values are treated as zeros.

NPV(rate, pmt1[, pmt2[, ...]])

rate the interest rate

pmt a number or a range - the payment amounts

Given an interest rate of rate and payments pmt1, pmt2, etc., the NPV function returns the Net Present Value of the stream of payments. Each payment can be a value or a range. If it is a range, each of the numbers in the range is included in the calculation. You may list any combination of numbers, cell addresses, and ranges as arguments for pmt.

An apartment building is being offered for sale for $400,000. Expected rental incomes for years 1-5 are $79,000, $85,000, $85,000, $103,000 and $120,000 and the interest rate is expected to stay close to 7%. Is this a worthwhile investment?

A

1 -400000

2 79000

3 85000

4 85000

5 103000

6 120000

To figure out the net present value of the cash in-flows:

@NPV(7%,A2:A6) -> $381,595

To figure out the value of the project:

Project Value = Initial Cost + NPV

+A1+@NPV(7%,A2:A6) -> -18,404

No, this project is not worthwhile.

PMT

Calculates the size of payments required to pay off a loan.

PMT(rate, per, loan[, fv[, type]])

loan the amount borrowed

rate the interest rate

per the number of payment periods

fv the [optional] Future Value of the loan

type the [optional starting time of the first payment

Given a loan of loan, an interest rate of rate, and per payment periods, the @PMT function returns the size of the payments. If the Future Value fv is not specified, it is assumed to be 0. If type is not specified, payments are assumed to start immediately.

What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%?

=PMT(9%/12,5*12,-40000) -> $830.33 per month.

@PMT

Calculates the size of payments required to pay off a loan.

@PMT(prin, rate, per)

loan the amount borrowed

rate the interest rate

per the number of payment periods

Given a loan of loan, an interest rate of rate, and per payment periods, the @PMT function returns the size of the payments required.

What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%?

@PMT(40000,9%/12,5*12) -> $830.33 per month.

PV

Computes the Present Value of an investment.

PV(rate, per, pmt[, fv[, type]])

rate the interest rate

per the number of payment periods

pmt the amount of each payment

fv the [optional] Future Value of the investment

type the [optional] starting time of the payments

Given an interest rate of rate, per payment periods, and a periodic payment of pmt, the PV function returns the Present Value of the investment. If a future value fv is not given, it is assumed to be 0. If type is not zero, payments are assumed to start in the next period.

What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%?

=PV(12%,20,-30000) -> $224,083.31

What is the present value of the investment which promises to pay $225,000 at the end of 20 years, given an interest rate of 12%?

=PV(12%,20,-225000) -> $23,325.02

@PV

Computes the Present Value of an investment.

@PV(pmt, rate, per)

pmt the amount of each payment

rate the interest rate

per the number of payment periods

Given per payments of size pmt and an interest rate of rate, the @PV function returns the Present Value of the investment.

What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%?

@PV(30000,12%,20) -> $224,083.31

@RATE

Returns the interest rate for an investment, given the Future Value, Present Value, and number of payment periods.

@RATE(fv, pv, per)

fv the desired Future Value of the investment

pv the Present Value of the investment

per the number of payment periods

Given a Present Value of pv, the @RATE function returns the required interest rate necessary to achieve the desired Future Value of fv in per payment periods.

How much interest would a bank account need to earn for an investment of $67,000 to grow to $100,000 in 5 years or less?

@RATE(100000,67000,5) -> 8.34% or more.

SLNor @SLN

Returns the Straight-Line depreciation of an asset.

SLN(cost, slvg, life)

cost the original cost of the asset

slvg the salvage value of the asset

life the expected lifetime of the asset

Given an asset with an original cost of cost, a salvage value of slvg, and a lifetime of life, the SLN function returns the Straight-Line depreciation for the asset.

A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off each year?

=SLN(12000,2000,5) -> $2,000

SYDor @SYD

Calculates the Sum of the Years Digits depreciation for an asset.

SYD(cost, slvg, life, per)

cost the original cost of the asset

slvg the salvage value of the asset

life the expected lifetime of the asset

per the number of periods for the depreciation calculation

Given an asset with an original cost of cost, a salvage value of slvg, and a lifetime of life periods, the SYD function returns the Sum of the Years Digits depreciation for per periods.

A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 4?

year 1: =SYD(12000,2000,5,1) -> $3,333

year 2: =SYD(12000,2000,5,2) -> $2,667

year 4: =SYD(12000,2000,5,4) -> $1,333

@TERM

Computes the number of payments required to reach a desired Future Value with a given payment size and interest rate.

@TERM(pmt,rate,fv)

pmt the size of each payment

rate the interest rate

fv the desired Future Value

Given payments of size pmt and an interest rate of rate, the @TERM function returns the number of payments required to reach the desired Future Value fv.

How many $700 monthly payments would be needed to accumulate $250,000 in a bank account earning 4% interest?

@TERM(700,4%/12,250000) -> 235.6 monthly payments (taking almost 20 years).

 

Also go to:

Index help

Contents help