1 / 22

SEMESTER V

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 )

Download Presentation

SEMESTER V

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SEMESTER V QUESTIONSIN SPREADSHEET Dr. FaiyazGadiwallaHinduja College

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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)

  7. 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

  8. QUESTIONS IN SPREADSHEET

  9. QUESTIONS IN SPREADSHEET

  10. QUESTIONS IN SPREADSHEET

  11. QUESTIONS IN SPREADSHEET

  12. QUESTIONS IN SPREADSHEET Explain the use of the following functions • i. FV () • ii. ABS () • iii. MAX () • iv. PMT () • v .FLOOR ()

  13. QUESTIONS IN SPREADSHEET Q) Explain what are Sub-totals. Q) What is a Pivot table? Q) Explain Relative, Absolute and Mixed cell references.

  14. QUESTIONS IN SPREADSHEET

  15. 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.

  16. QUESTIONS IN SPREADSHEET

  17. QUESTIONS IN SPREADSHEET

  18. QUESTIONS IN SPREADSHEET

  19. QUESTIONS IN SPREADSHEET

  20. QUESTIONS IN SPREADSHEET

  21. 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.

  22. QUESTIONS IN SPREADSHEET

More Related