1 / 8

Financial Functions

Financial Functions. Overview. Power of Excel = Calculations Loan Calculations Expense Calculations Consolidate Values Search for Values. Loan Calculations. Open Excel & rename Sheet 1- Loan In cell A1- Date; A2- Principal; A3- Interest; A4- Years; A5- Payment

kieve
Download Presentation

Financial Functions

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

  2. Overview • Power of Excel = Calculations • Loan Calculations • Expense Calculations • Consolidate Values • Search for Values

  3. Loan Calculations • Open Excel & rename Sheet 1- Loan • In cell A1- Date; A2- Principal; A3- Interest; A4- Years; A5- Payment • In Cells B1- 6/1/2006; B2- $20,000; B3- 5.5%; B4- 5

  4. Insert Formula Function • Click in cell B5 (Payment) and click fx next to formula bar, search for- calculate loan payment, PMT, click OK • In the Function Arguments Box, type • Rate- B3(5.5%)/12 • Nper- B4(5)*12 • PV- B2(20,000) • Click OK • Amount is red- it’s a payment (negative) • Change #’s to see payment change

  5. Name Cell Ranges • Look in formula bar to see formula for cell • Select the entire range of cells A1:B5 • You can name a range of cells by selecting the range and going to Insert, Name, Create • Uncheck top row if you don’t have headings there • Name box reflects name change • Formulas are easier to read with names

  6. Amortization Table • Drag the sheet you were just working with over while holding the Ctrl key down to copy it • Rename it Amortization • Make D1 =B1, click and drag to fill months • In cell E1, Type =B2

  7. Calculate Future Value • While in cell E2, click the fx button, type future value of a loan- FV, click OK • Rate- B3(Interest Rate)/12 • Nper- 1 • Pmt- B5 (Payment) • PV- E1 (Principal) • It is red for negative, type a – between = and FV (makes it positive) • Click & Drag formula down, use format painter • To add more months, select last two rows and drag down

  8. Dynamic Dates • Select cell D2- click fx, Date • Year- YEAR(D1) • Month- MONTH(D1) +1 • Day- DAY(D1) OR 1 (first of the month) • Drag D2 down to the end of the table • Now dates will change automatically in Amortization Table when you change the date in the Payment Calculator

More Related