Microsoft Excel Notes

Microsoft Excel Notes

Relative reference - when the "fill handle" is dragged and formulas change automatically adjusting for the new position.

Absolute reference - when the "fill handle" is dragged the same cell reference is kept where a "$" is added.

3-D Reference - combines sheet reference and cell reference.

l For example: =SUM(Sheet1:Sheet3!B1:B3) adds all the values from B1 to B3 in all the sheets from Sheet1 to Sheet3.

l The "!" separates the sheet reference from the cell reference.

l The ":" between the first and third sheet says itshould include all the sheets between Sheet1 and Sheet 3.

l The ":" between the first and the third cell says it should include all the cells between B1 and B3.

Pivot Table - an interactive table enabling you to quickly group and summarize large amounts of data.

Monthly Payment Function given yearly InterestRate and yearly LoanTerm:

l =PMT(InterestRate/12,LoanTerm*12,-LoanAmount)

For monthly payments given yearly Interest rate and yearly Loan Term:

l =PMT(InterestRate/12,LoanTerm*12,-LoanAmount)

For yearly payments given yearly Interest rate and yearly Loan Term:

l =PMT(InterestRate,LoanTerm,-LoanAmount)

For monthly payments given monthly Interest rate and monthly Loan Term:

l =PMT(InterestRate,LoanTerm,-LoanAmount)

For monthly payments given yearly Interest rate and monthly Loan Term:

l =PMT(InterestRate/12,LoanTerm,-LoanAmount)

All these are functions formonthlypayments:

Ø Payment Function:

l =PMT(InterestRate/12,LoanTerm*12,-LoanAmount)

Ø Principal Payment Function:

l =PPMT(InterestRate/12,Period,LoanTerm*12,-LoanAmount)

Ø Interest Payment Function:

l =IPMT(InterestRate/12,Period,LoanTerm*12,-LoanAmount)

Ø Beginning Balance = Present Value

l =PV(InterestRate/12,LoanTerm*12,-Payment)

VLOOKUP looks for a value from a given cell in the leftmost column of a table and then returns a value in the same row from the column you specify.

Ø =VLOOKUP(cell_of_lookup_value, cell_range_of_the_table, corresponding_column)

Ø For example if the cell range is B3 to F6 and the lookup cell is A2 and the corresponding value is in column 3 of the cell the equation would be:

Ø =VLOOKUP(A2,B3:F6,3)

IF is a logical function which checks to see if a condition is met, and returns one value if TRUE, and another value if FALSE.

Ø =IF(logical_test, value_if_true, value_if_false)

Ø For example, if $500 will be added to the price of a car if the down payment is less than $1000 and $0 will be added if the down payment is greater than or equal to $1000 then the function would be:

Ø =IF(DownPayment
Another IF example:

Ø For example, if there is a $300 assessment fee if the monthly payment plus $30 is less than $600 and $0 otherwise, then the function would be:

Ø Logical_test: (Payment+30)
Ø Value_if_true: 300

Ø Value_if_false:0

Ø =IF((Payment+30)
Loan Payment Formula:

Payment= (Interest+ [Interest/[(1+Interest)^Loan Term]-1])X Loan Amount

Ø Example:

l Interest = 8% Annually

l Loan Amount = $150,000

l Loan Term = 30 years

Ø To find Monthly Payment

Ø Monthly Rate = .08/12 (convert to monthly) = 0.0066667

Ø Loan Amount = $150,000

Ø Loan Term = 30 x 12 (convert to months) = 360 months