An Amortization Schedule

1. An Amortization Schedule New Functions

2. An Amortization Schedule • An Amortization Schedule • Payment schedule on a loan • Shows date of each payment • Amount that goes towards interest • Amount that goes toward principle • The remaining balance • Completely Flexible • Incorporate the option to make an extra payment

3. These functions Check whether a condition is met Return one of two values True or False AND OR NOT IF FALSE TRUE Logical Functions

4. AND Logical Function • Checks whether all arguments are true and returns the value true (otherwise returns False)

5. OR Function • Checks whether any of the arguments are true and returns true or false • Returns false only if all arguments are false

6. Additional Logical Functions • =NOT • Changes FALSE to TRUE and TRUE to FALSE • =NOT(1+1=2) • Reverses an equation that evaluates to TRUE (FALSE) • =NOT(FALSE) Reverses FALSE to TRUE • =TRUE • Returns the value TRUE • No arguments • Used for compatibility with other spreadsheet programs • =FALSE • Returns the value FALSE

7. Date Functions • Excel stores a date as a serial number • Jan. 1, 1900 = 1; Jan. 2, 1900 = 2 • Want the same date in every month • Payment Schedule • Jan. 15, Feb. 15, March 15, etc. are due dates • Problem • Add 30 or 31, or 28 (29) for February

8. =MONTH(serial_number) • Returns the numeric month of a date represented by a serial number

9. =DAY(serial_number) and =YEAR(serial_number) =Month(A1)+1 adds 1 to the numeric month

10. The DATE Function • The DATE function is most useful in formulas where year, month, and day are formulas, not constants • =DATE(year,month,day) • Returns the sequential serial number that represents a particular date • Year  • Argument can be one to four digits

11. DATE Function=DATE(year,month,day) • Month    • A number representing the month of the year • If month is greater than 12 • Date adds the months greater than 12 to the first month in the year specified • If month = 14, Excel would return February of the next year • =DATE(2008,14,2) returns the serial number representing February 2, 2009 • Day    • A number representing the day of the month • If day is greater than the number of days in the month specified • Date adds the extra number of days to the first day in the next month • =DATE(2008,1,35) returns the serial number representing February 4, 2008

12. An Example • Wish to know the next scheduled payment • =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

13. DATE Function =DATE(L13,J13+1,K13)

14. MATCH Function • Returns the relative position of an item in an array that matches a specified value in a specified order • Use MATCH instead of a LOOKUP function • when you need the position of an item in a range instead of the item itself • =MATCH(lookup_value,lookup_array,match_type)

15. =MATCH • Lookup_value • The value you want to match in lookup_array • Lookup_value can be a value (number, text, or logical value) or a cell reference • Lookup_array   • a contiguous range of cells containing possible lookup values • Lookup_array must be an array or an array reference • Match_type    • is the number -1, 0, or 1 • Specifies how Excel matches lookup_value with values in lookup_array

16. =MATCH • If match_type is 1 • MATCH finds the largest value that is less than or equal to lookup_value • Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE • If match_type is 0 • MATCH finds the first value that is exactly equal to lookup_value • Lookup_array can be in any order • If match_type is -1 • MATCH finds the smallest value that is greater than or equal to lookup_value • Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on • If match_type is omitted, it is assumed to be 1

17. Look-upArray is C2:C5 Look-up Value

19. INDEX Function • Returns a value from within a table or range • Returns the value at the intersection of a row and column • =INDEX(array,row_num,column_num) • Returns the value of a specified cell within array

20. =Index(Array,row,column)

21. Principal Payments • Without extra payments • Use PPMT function • Returns the payment on the principal • For a given period • For an investment based on periodic, constant payments • For constant interest rate • =PPMT(rate,per,nper,pv,fv,type) • Rate    • The interest rate per period

22. PPMT =PPMT(rate,per,nper,pv,fv,type) • Per    • Specifies the period and must be in the range 1 to nper • Nper    • Total number of payment periods in an annuity • Pv    • The present value • The total amount that a series of future payments is worth now • Fv    • The future value, or a cash balance you want to attain after the last payment is made. • If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0 • Type    • The number 0 or 1 and indicates when payments are due

23. Interest Payments • Without an extra payment • Use IPMT • Returns the interest payment • For a given period for an investment • Based on periodic, constant payments • A constant interest rate • =IPMT(rate,per,nper,pv,fv,type) • Rate    • The interest rate per period • Per    • The period for which you want to find the interest • Must be in the range 1 to nper

24. IPMT • Nper    • The total number of payment periods in an annuity • Pv    • The present value, or the lump-sum amount that a series of future payments is worth right now • Fv    • The future value, or a cash balance you want to attain after the last payment is made • If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0) • Type    • When payments are due • the number 0 (end of period) • The number 1 (beginning of period) • If omitted, type is assumed to be 0