I. Calculation of present value
In Excel, the function of calculating present value is PV, and its grammatical format is PV(rate, nper, pmt, [fv], [type]). Among them, the parameter rate is the interest rate of each period, the parameter nper is the number of investment periods (or payment periods), and the parameter pmt is the payment amount of each period. If pmt parameter is omitted, fv parameter cannot be omitted; The fv parameter is a future value. If the fv parameter is omitted, it is assumed that its value is 0, that is, the future value of the loan is zero. At this point, the pmt parameter cannot be omitted. The value of the type parameter is 1 or 0, which is used to specify whether the payment time starts or ends. If type is omitted, the value is assumed to be 0, that is, the default payment time is at the end.
Case 1: calculating the present value of compound interest. An enterprise plans to get a sum of 65,438+0,000,000 yuan in five years. Assuming that the annual return on investment is 65,438+00%, how much money should be invested at one time now?
Enter: =PV( 10%, 5,0,-100000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 62092 1.32 yuan.
Case 2: Calculate the present value of ordinary annuity. The subscription cost of a fund is 80,000 yuan, and the fund can return to 600 yuan at the end of each month in the next 20 years. If the required minimum annual rate of return is 8%, is it worthwhile to invest in this fund? Enter: =PV(8%/ 12, 12*20, -600) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 7 1732.58 yuan. 7 1732.58 yuan is the investment amount. If the actual purchase cost is 80,000 yuan, it is not worthwhile to invest in this fund.
Case 3: Calculate the present value of annuity in advance. There is a five-year installment business to buy equipment, which pays 500 thousand at the beginning of each year, and the actual annual interest rate of the bank is 6%. How much is the total installment payment of this business equivalent to the current one-time payment? Enter: =PV(6%, 5, -500000, 0, 1) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 2232552.5438+0 yuan. In other words, at present, the total installment payment of this business is equivalent to a one-time payment of 2238+0 yuan. 38860.8888888868 1
Second, the calculation of net present value
In Excel, the function of calculating net present value is NPV, and its grammatical format is: NPV(Rate, value 1, value2, ...). Rate is a fixed discount rate for a certain period; The value 1, the value 2, ... is a series of cash flows, representing expenditure or income. NPV function can be used to calculate the total present value of future investment or expenditure, future income and net cash flow.
Case 4: Calculate the total present value of installment (payment). On June 5438+1 October1in 2007, Company A purchased a piece of equipment from Company B, which has been put into use. According to the contract, the total equipment price is 6,543,800,000 yuan, which will be paid in three years, that is, 5 million yuan in February 2007, 3 million yuan in February 2008 and 65,438+0 in February 2009. Suppose the annual interest rate of a 3-year bank loan is 6%. It is required to calculate the present value of the total equipment price.
When recording fixed assets, we must first determine the present value of the total equipment price. The specific operation is: enter =NPV(6%, 500,300,200) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 9,066,200 yuan. The results also show that if the payment is paid in one lump sum and Party B agrees to calculate the annual interest rate of 3-year bank loan at 6%, the current transaction amount should be 9,066,200 yuan.
Case 5: Calculate the net present value of investment projects. The initial investment of a project is 206,000 yuan, and the cash flow at the end of each year from/kloc-0 to 6 years is 50,000 yuan, 50,000 yuan, 50,000 yuan, 48,000 yuan and 1 06,000 yuan respectively. If the discount rate is 12%, you need to calculate the net present value of the project investment.
Enter: =NPV( 12%, 50000, 50000, 50000, 48000, 106000)-206000 in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 26806.86 yuan.
Third, the final value calculation
In Excel, the function to calculate the final value is FV, and its syntax format is: FV(rate, nper, pmt, [pv], [type]). Among them, the parameter rate is the interest rate of each period, the parameter nper is the number of periods, and the parameter pmt is the amount paid in each period. Omitting pmt parameter cannot omit pv parameter; Parameter pv is the current value. Omitting the parameter pv means that its value is assumed to be zero. At this point, the pmt parameter cannot be omitted. The value of the type parameter is 1 or 0, which is used to specify whether the payment time starts or ends. If type is omitted, the value is assumed to be 0, that is, the default payment time is at the end.
Case 6: Calculate the final value of simple interest and interest. Deposited in the bank 10000 yuan, with a term of 5 years, and the bank pays interest at a simple interest rate of 5 years. How much money can I withdraw from the bank at one time after 5 years? How much is the interest?
Enter: = 10000*( 1+5%) in the cell of Excel worksheet, and press enter to confirm. The result is 10500 yuan (the amount that can be withdrawn from the bank at one time after 5 years). Enter = 10000*5% in the cell of Excel worksheet, and press enter to confirm, and the result will be displayed as 500 yuan (interest).
Case 7: Calculate the final value of compound interest and interest. Borrow 6,543,800,000 yuan from the bank, with an annual interest rate of 8%, with a term of 5 years, and repay the principal and interest once at maturity. How much should I pay back in five years? How much interest is there?
Enter: =FV(8%, 5,-1000) in the cell of Excel worksheet, and press enter to confirm. The result (the final value of compound interest, that is, the sum of principal and interest) is1469,333,300 yuan. Enter: =FV(8%, 5,-1000)- 1000 in the cell and press enter to confirm. The result shows that it is 4,693,300 yuan (interest).
Case 8: Calculate the final value of ordinary annuity. An enterprise plans to deposit 20 thousand yuan at the end of each month from now on. If the monthly interest rate is 0.353%, what will be the account balance after two years?
Enter: =FV(0.353%, 24, -20000) in the cell of Excel worksheet, and press enter to confirm. The result will be automatically displayed as: 499,999.50 yuan, that is, the account balance will be 499,999.50 yuan after two years.
Case 9: Calculate the final value of annuity in advance. An enterprise plans to deposit 20,000 yuan at the beginning of each month from now on. If the monthly interest rate is calculated at 0.353%, what will be the account balance after two years?
Enter: =FV(0.353%, 24, -20000, 0. 1) in the cell of Excel worksheet, and press enter to confirm. The result will be automatically displayed as 50 1764.50 yuan, that is, the balance of the account will be 50 1764.50 yuan after two years.
Fourth, the calculation of discount rate
In the Excel worksheet, the function for calculating the discount RATE is rate, and its grammatical format is: RATE(nper, pmt, pv, [fv], [type], [guess]). Where guess is the expected (guessed) interest rate, and if the expected interest rate is omitted, the assumed value is 10%.
Case 10: calculating the rate of return. At present, it is 15000 yuan. If it reaches 50,000 yuan after 10 years, what is the lowest acceptable rate of return when choosing investment projects?
Enter: =RATE( 10, 15000, -50000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 12.795% (rounding results, decimal places can be reserved as required, the same below).
Case 1 1: calculating interest rate. Someone suggested that you lend him 30,000 yuan and promise to pay you back 9,000 yuan at the end of each year for five years. Should you accept this offer? Enter: = rate(59000, -30000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 15.24%. The results show that if the rate of return of 65,438+05.24% is higher than that of other investment projects, the scheme is acceptable.
Case 12: calculating the discount rate of installment payment. A company sells a set of equipment, and it is agreed to pay by installments in five years from the end of the sales year, with an annual income of 2 million yuan, totaling 6,543,800,000 yuan (excluding VAT). Assuming that the buyer pays the purchase price on the sales establishment date, it is enough to pay 8 million yuan. The 8 million yuan paid by the buyer on the sales establishment date can be regarded as the fair value of the receivable amount. The accounting treatment of this business involves the calculation of discount rate, that is, the data of "unrealized financing income" and "financial expenses" should be calculated at the end of each year. First of all, we should calculate the discount rate of 2 million yuan annuity, the number of five-year periods and the present value of 8 million yuan. Enter: = rate (5 5,200, -800) in the cell of Excel worksheet, and press enter to confirm. The result is 7.93%.
Calculation of the number of cycles of verbs (abbreviation of verb)
In Excel, the function of calculating the number of periods is NPER, and its grammatical format is NPER(rate, pmt, pv, [fv], [typ]).
Case 13: Calculate the accumulation period of funds. The existing capital of an enterprise is 100000 yuan, and the annual return rate of investment projects is 8%. How many years later can the existing capital be increased to 200,000 yuan? Enter: =NPER(8%, 0, 100000, -200000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 9 years.
Case 14: calculating the service life of the equipment in the comparison scheme. An enterprise plans to buy a diesel engine or a gasoline engine. Diesel engines can save 5,000 yuan in fuel costs every month compared with gasoline engines, but the price of diesel engines is 50,000 yuan higher than that of gasoline engines. Suppose the annual rate of return of funds is 18%, and the annual turnover of funds is more than 12 times (compound interest once a month). How many years does it take for a diesel engine to be economical? Enter = nper (18%12,5000, -50000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 1 1 year.
Case 15: Calculating the repayment period. Mortgage to buy a house, with a down payment of 600,000 yuan. Suppose the annual interest rate of the loan is 7.95% and the monthly repayment capacity is 5,000 yuan. How many years will it take to pay off the loan? Enter: = nper (7.95%/12,5000,-600,000)/12 in the cell of Excel worksheet, and press enter to confirm. The results show that 20 years.
Calculation of equal income (payment) of intransitive verbs
In Excel, the function of calculating equal payment (payment) is PMT, and its grammatical format is PMT(rate, nper, pv, [fv], [type]).
Case 16: annuity calculation of investment recovery. Suppose you borrow 20,000 yuan with an annual interest rate of 10% to invest in a project with a life of 10 years. How much money should I get back at least every year? Enter =PMT( 10%, 10, -20000) in the cell of Excel worksheet, and press enter to confirm, and the result will be automatically displayed as 3254.5438+0 yuan.
Case 17: Calculation of installment amount under mortgage. The mortgage loan is 600,000 yuan, assuming it is paid off in 25 years, and the annual interest rate of the loan is 8%. Q: What is the principal and interest to be paid at the end of each month? If the repayment is made at the beginning of each month, what is the monthly repayment amount? Enter: = PMT (8%/12,25 *12,600,000) in the cell of Excel worksheet, and press enter to confirm that the calculated monthly repayment amount is 4630.90 yuan. Enter: =PMT(8%/ 12, 25* 12, -600000, 0, 1) in the Excel cell, and press enter to confirm that the calculated monthly repayment amount is 4600.23 yuan.
Case 18: Pension Savings Planning. An enterprise plans to make a pension plan for a group of employees who retire after 30 years. After retirement, these employees can receive 2500 yuan from the bank at the end of each month for 25 years in a row. If the annual compound interest of the deposit is 3%, how much money will the enterprise need to deposit in the bank for each of these employees every year from this year? Enter: =PMT(3%, 30,0,-PV (3%/12,25 *12, -2500)) in the cell of Excel worksheet, and press enter to confirm that the result will be1108. That is, enterprises need to deposit each employee in the bank11081.17 yuan every year from this year.
This example involves the nesting of Excel functions, which is difficult for accountants who are not familiar with the application of Excel functions. The key interpretation of the formula is given here:-PV (3%/12,25 *12,2500) belongs to the fv parameter of PMT function as a whole. The calculation result of-PV (3%/12,25 *12,2500) is the amount of funds needed after 30 years. For PMT function, it is clear how much the final value will reach after 30 years, and then how much money will be deposited in the bank every year can be calculated. The fund needed after 30 years is the total present value of the monthly payment for 25 years.