|
![]() |
![]() |
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). |
||
|
||