220 likes | 299 Views
SEMESTER V. QUESTIONS IN SPREADSHEET Dr. Faiyaz Gadiwalla Hinduja College. QUESTIONS IN SPREADSHEET. FV(): Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Syntax FV ( rate , nper , pmt , pv , type )
E N D
SEMESTER V QUESTIONSIN SPREADSHEET Dr. FaiyazGadiwallaHinduja College
QUESTIONS IN SPREADSHEET • FV():Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Syntax • FV(rate, nper, pmt, pv, type) • rate is the interest rate per period. • nper is the total number of payment periods in an annuity. • pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. • pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. • type is the number 0 or 1 and indicates when payments are due. 0 or omitted indicates payments are made at the end of the period, while 1 indicates that they are made at the beginning of the period.
QUESTIONS IN SPREADSHEET • Suppose you wants to start saving Rs.100 every month for a child’s college education, starting from the first month of child’s birth and want to know what would be the amount after 18 years at the rate of interest 5% compounded annually, then the future amount can be computed using the function. • = FV(5%/12,18*12,-100,0,0) • This function return 34,920.20 which is the amount returned to you.
QUESTIONS IN SPREADSHEET • PV(): The PV function is used to determine how much a specific future amount is worth today • Syntax PV(rate,nper,pmt,fv,type) • Suppose you want to take car loan of Rs.4,00,000 and a company offers an installment of Rs.9100 per month for 5 years to pay back this loan, assuming that interest rate is 12% P.A., the following formula will compute the present value of the amount. • =PV(12%/12,5*12,-9100) • This function returns 409,090.85 which is the present value of the amount you are paying.
QUESTIONS IN SPREADSHEET • PMT(): Calculates the payment for a loan based on constant payments and a constant interest rate. Syntax • PMT(rate,nper,pv,fv,type) • Suppose you want buy a refrigerator worth Rs. 44,000/- and you want to know how much you have to pay as monthly payments when you are making down payment of Rs. 4000/- and the dealer is offering 4% financing for a 2 year loan. • =PMT(4%/12,2*12,40000,0,0) • This function returns 1,737.00. This is your installment amount.
QUESTIONS IN SPREADSHEET • IPMT (Interest payment) function can be used to find the interest part of a payment based on periodic, constant payments and a constant interest rate.. • PPMT (Principal payment) function can be used to find the principal part of a Payment based on periodic, constant payments and a constant interest rate.. • These two functions are useful when you need to determine the interest/principal breakdown of a particular payment. • Also PMT = IPMT + PPMT • The syntax of the two functions are as follows: • IPMT(rate, per , nper , pv , fv ,type) • PPMT(rate, per , nper , pv , fv ,type)
QUESTIONS IN SPREADSHEET • NPV() : Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Syntax • NPV(rate,value1,value2, ...) • e.g. =NPV(10%,-10000,3000,4200,6800) gives Rs 1,188.44
QUESTIONS IN SPREADSHEET Explain the use of the following functions • i. FV () • ii. ABS () • iii. MAX () • iv. PMT () • v .FLOOR ()
QUESTIONS IN SPREADSHEET Q) Explain what are Sub-totals. Q) What is a Pivot table? Q) Explain Relative, Absolute and Mixed cell references.
QUESTIONS IN SPREADSHEET Let us say you have taken a car loan of Rs 1, 00,000 from a bank. You have agreed to pay Rs 2700 at the end of each month for next 5 years. The bank interest is 12% P.A. write down the procedure to find out the present value.
QUESTIONS IN SPREADSHEET Cost of machinery is entered in cell A4, estimated life in years is 5 is entered in cell A6 this asset has no salvage value. Explain how you will create an EXCEL worksheet showing year, depreciation, and WDV use reducing balance method.