APPENDIX C

EXCEL FORMULAS: SIMPLIFYING THE CALCULATIONS

Accelerated Depreciation

A1:

basis of asset

B1:

period (in years)

C1:

acceleration percentage

D1:

=SUM(A1/B1)*C1

Accumulated Value of 1

A1:

principal amount

A2:

interest rate

A3:

number of periods per year

A4:

number of years

B4:

=FV(r/n,1*r,,-1000)

Accumulated Value of 1 per Period

FV(r/n,y*d)

where:

r

= interest rate

n

= number of periods per year

y

= number of years

d

= amount of periodic deposits

Adjusted Basis in Property

A1:

purchase price

B1:

closing costs

C1:

deferred gain

D1:

= SUM(A1+B1-C1)

Adjusted Purchase Price

A1:

purchase price

B1:

closing costs

C1:

=SUM(A1+B1)

Adjusted Sales Price

A1:

sales price

B1:

closing costs

C1:

=SUM(A1-B1)

After-Tax Cash Flow

A1:

rental income

B1:

cash expenses

C1:

depreciation

D1:

principal payments

E1:

capital expenditures

F1:

income tax savings

G1:

=SUM(A1-(B1+C1)-(D1+E1-F1)

Amortization Payment

=PMT(R/P,T,B)

where:

PMT

= payment

R

= annual rate

P

= number of payments per year

T

= total number of payments

B

= amount borrowed

Annual Percentage Rate

=RATE(T,-M,B)*12

where:

T

= total number of payments

M

= monthly payment

B

= amount borrowed

Annualized Return (Using Months)

A1:

return over entire holding period

B1:

holding period (number of months)

C1:

=SUM((A1/B1)*12)

Annualized Return (Using Years)

A1:

return over entire holding period

B1:

holding period (number of years)

C1:

=SUM(A1/B1)

Area of a Circle

A1:

3.1429

B1:

radius

C1:

=SUM(3.1429*(B1*B1))

Area of a Square or Rectangle

A1:

length

B1:

width

C1:

=SUM(A1*B1)

Area of a Trapezoid

A1:

base number A

B1:

base number 2

C1:

altitude

D1:

= SUM[(A1+B1)/2]*C1

Area of a Triangle

A1:

base

B1:

altitude

C1:

=SUM(A1/B1)/2

Assessment Ratio

A1:

assessed value

B1:

asked price

C1:

=SUM(A1/B1)

Balance Sheet

A1:

assets

B1:

liabilities

C1:

net worth

D1:

= SUM(B1+C1)

E1:

= SUM(A1-D1) (check for zero balance)

Bank Reconciliation

A1:

checkbook balance

A2:

checkbook errors (with – sign if negative)

A3:

=SUM(A1+A2)

B1:

bank statement balance

B2:

deposits in transit total

B3:

outstanding check total

B4:

bank errors (with – sign if negative)

B5:

= SUM(B1+B2-B3+B4)

C1:

=SUM(A3-B5) (check for zero balance)

Breakeven After Taxes and Inflation

A1:

inflation rate

B1:

effective tax rate

C1:

=SUM(A1/(100-B1))

Breakeven Ratio

A1:

operating expenses

B1:

mortgage debt service

C1:

effective gross income

D1:

= SUM(A1+B1)/C1

Capital Gain or Loss

A1:

sale price

B1:

closing costs at sale

C1:

carry over losses

D1:

depreciation claimed

E1:

purchase price

F1:

closing costs at purchase

G1:

deferred gains

H1:

=SUM(A1-B1-C1+D1-E1+F1-G1)

Capitalization Rate

A1:

annual net income

B1:

purchase price

C1:

=SUM(A1/B1)

Carryover Loss Allocation

A1:

loss reported for the property

B1:

total net loss, all properties

C1:

=SUM(A1/B1)

Cash Income

A1:

net income

B1:

depreciation expense

C1:

=SUM(A1/B1)

Cash-on-Cash Return

A1:

annual cash flow

B1:

cash investment

C1:

= SUM(A1/B1)

Checking Account Math Verification

A1:

ending balance

B1:

total of checks

C1:

total of deposits

D1:

= SUM(A1+B1-C1)

Closing Prorated Days (Buyer)

A1:

days remaining as of prior month end

B1:

days in partial month

C1:

=SUM(A1-B1)

Closing Prorated Days (Seller)

A1:

days used as of prior month end

B1:

days in partial month

C1:

=SUM(A1+B1)

Closing Prorated Interest

A1:

loan amount

B1:

interest rate

C1:

days of prorated interest

D1:

days in the month

E1:

= SUM((A1*B1)/12)*(C1/D1)

Closing Prorated Property Taxes

A1:

total property tax bill, half-year

B1:

days of responsibility

C1:

days in liability period

D1:

= SUM(A1*(B1/C1))

Conversion, Acres to Square Feet

A1:

acres

B1:

=SUM(A1*43560)

Conversion, Inches to Feet

A1:

inches

B1:

=SUM(A1/12)

Conversion, Percentage to Decimal

A1:

interest rate

B1:

=SUM(A1/100)

Conversion, Square Feet to Acres

A1:

square feet

B1:

=SUM(A1/43,560)

Conversion, Yards to Feet

A1:

yards

B1:

=SUM(A1*3)

Cost Approach

A1:

cost of improvements

B1:

depreciation

C1:

land value

D1:

= SUM(A1-B1+C1)

Cost of Financed Property

A1:

monthly payment

B1:

number of months

C1:

down payment

D1:

= SUM(A1*B1)+C1

Current Ratio

A1:

current assets

B1:

current liabilities

C1:

=SUM(A1/B1)

Current Yield (Bond)

A1:

nominal yield

B1:

premium or discount

C1:

=SUM(A1/B1)

Current Yield (Stock)

A1:

dividend per share

B1:

current price per share

C1:

=SUM(A1/B1)

Daily Compounding

A1:

annual interest rate

B1:

=SUM(A1/365)

Debt Coverage Ratio

A1:

net operating income

B1:

mortgage payment

C1:

=SUM(A1/B1)

Debt-to-Equity Ratio

A1:

long-term debt

B1:

equity

C1:

=SUM(A1/(A1+B1))

Depreciation (Appraisal)

A1:

economic life

B1:

=SUM(100/A1)

C1:

=SUM(A1/B1)

Depreciation Basis (Appraised Value)

A1:

improvement value per appraisal

B1:

total appraised value

C1:

=SUM(A1/B1)

Depreciation Basis (Assessed Value)

A1:

improvement value per assessment

B1:

total assessed value

C1:

=SUM(A1/B1)

Depreciation Basis (Insured Value)

A1:

insurance limits of liability, dwelling

B1:

total basis in the property

C1:

=SUM(A1/B1)

Discount Yield

A1:

appraised value

B1:

asked price

C1:

=SUM(A1-B1)/B1

Double-Entry Bookkeeping, Basic Formula

A1:

debit balances

B1:

credit balances

C1:

=SUM(A1-B1) (check for zero balance)

Economic Rent per Room

A1:

rent per period

B1:

number of rooms

C1:

=SUM(A1/B1)

Economic Rent per Square Foot

A1:

rent per period

B1:

square feet

C1:

=SUM(A1/B1)

Economic Rent per Unit

A1:

rent per period

B1:

number of units

C1:

=SUM(A1/B1)

Equity

A1:

current market value

B1:

balance, mortgage debt

C1:

=SUM(A1-B1)

Equity Dividend Yield

A1:

net cash flow

B1:

down payment

C1:

=SUM(A1/B1)

Equity Return

A1:

cash income

B1:

principal reduction

C1:

down payment

D1:

= SUM(A1+B1)/C1

Estimated Monthly Payment

A1:

payment, higher interest rate

B1:

payment, lower interest rate

C1:

number of rates

D1:

= SUM(A1+B1)/2

Exercise Cost (to Owner)

A1:

current market value

B1:

fixed option price of the property

C1:

option cost

D1:

= SUM((A1-B1)-C1)/B1

Exercise Return (to Tenant)

A1:

option cost

B1:

current market value

C1:

fixed option price of property

D1:

= SUM(A1/(B1-C1))

Expense Allocation (Even Distribution)

A1:

nonspecific expenses

B1:

number of properties

C1:

=SUM(A1/B1)

Expense Allocation (Months Owned)

A1:

months of ownership during the year

B1:

total months of ownership, all properties

C1:

=SUM(A1/B1)

Expense Allocation (Prepayments)

A1:

total prepaid expense

B1:

number of months the expense relates to

C1:

=SUM(A1/B1)

Expense Allocation (Revenue Share)

A1:

revenue received for the property

B1:

total revenue, all properties

C1:

=SUM(A1/B1)

Expense Allocation (Square Feet)

A1:

rentable square feet, each unit

B1:

total square feet, all units

C1:

=SUM(A1/B1)

Expense Ratio

A1:

operating expenses

B1:

gross income from rents

C1:

= SUM(A1/B1)

Exponential Moving Average

Based on example of six initial values plus a seventh value:

A1:

Number of fields

A2:

=SUM(2/A1)

A3:

Value 1

A4:

Value 3

A5:

Value 3

A6:

Value 4

A7:

Value 5

A8:

Value 6

A9:

=SUM(A3:A8)/A1

A10:

= SUM(A10*A2)

A11:

=SUM(A11+A9)

Floor-Area Ratio

A1:

building area

B1:

land area

C1:

=SUM(A1/B1)

Gross Rent Multiplier

A1:

sales price

B1:

rent per period

C1:

=SUM(A1/B1)

Half-Year Convention

A1:

basis of the asset

B1:

=SUM(A1/2)

Income Statement

A1:

revenues

B1:

costs

C1:

expenses

D1:

= SUM(A1-(B1+C1))

Loan-to-Value Ratio

A1:

loan balance

B1:

value

C1:

=SUM(A1/B1)

Loss Ratio

A1:

nonrental area

B1:

gross building area

C1:

=SUM(A1/B1)

Market or Sales Comparison Approach

A1 . . . A3:

comparable property values (in this example, three properties)

A4:

plus or minus adjustments

B4:

=SUM(A1:A4)/3

Math Check, Change in Loan Balance

A1:

previous balance

B1:

new balance

C1:

=SUM(A1-B1)

Math Check, Interest/Principal

A1:

principal amount

B1:

interest amount

C1:

=SUM(A1+B1)

Maximum Loss Allowance

A1:

adjusted gross income

B1:

=SUM(25000-(A1-100000)/2)

Mid-Month Convention

A1:

basis of the asset

B1:

number of half-month periods

C1:

=SUM(A1/24)*B1

Monthly Compounding

A1:

principal amount

A2:

interest rate

A3:

12

A4:

number of years

B4:

=FV(0.04/12,1*12,,-1000)*

*In this example, the assumed principal amount is $1,000 in cell B4. This should be adjusted to the correct value. In addition, the use of “1” limits the calculation to monthly compounding for only one year.

Monthly Loan Amortization

=SUM(P-(T-(P*(i/12))))

where:

P

= previous balance, mortgage loan

T

= total payment

i

= interest rate

Months of Property Inventory on the Market

A1:

total inventory of properties

B1:

average sales per month

C1:

=SUM(A1/B1)

Moving Average

Based on example of 12 values in the field:

A1:

through A12: values of V1 . . . V12

B1:

number of values in the field

C12:

=SUM(A1:A12)/B12

Net Current Value of Property

A1:

cost

B1:

rate of depreciation (decimal equivalent)

C1:

effective age

D1:

= SUM(A1-(A1*B1*C1))

New Basis in 1031 Exchange

A1:

adjusted purchase price

B1:

deferred gain

C1:

=SUM(A1-B1)

Occupancy Rate

A1:

occupied units

B1:

total units

C1:

=SUM(A1/B1)

Operating Expense Ratio

A1:

operating expenses

B1:

rental income

C1:

=SUM(A1/B1)

Option to Exercise Ratio

A1:

option price

B1:

sale price upon exercise

C1:

=SUM(A1/B1)

Partial Month’s Rent Liability

A1:

number of days

B1:

full month

C1:

=SUM(A1/B1)

Payback ratio

A1:

cash investment

B1:

net cash flow

C1:

=SUM(A1/B1)

Periodic Rate

A1:

annual interest rate

B1:

number of period

C1:

=SUM(A1/B1)

Pi

A1:

circumference of a circle

B1:

diameter of a circle

C1:

=SUM(A1/B1)

Present Value of 1

=PV(r,p,0,FV)

When r

= interest rate

P

= number of periods

0

= starting point (beginning of period)

FV

= future value

Profit Margin

A1:

cash flow

B1:

effective gross income

C1:

=SUM(A1/B1)

Prorated Rent, Partial-Year Use

A1:

rental period (days)

B1:

full year (365 or 360)

C1:

=SUM(A1/B1)

Prorated Rent, Tenant Share

A1:

tenant’s square-foot share

B1:

total square feet

C1:

=SUM(A1/B1)

Quarterly Compounding

A1:

interest rate

B1:

=SUM(A1/4)

Rate of Return

A1:

current value

B1:

original cost or basis

C1:

=SUM(A1-B1)/B1

Return on Equity

A1:

proceeds upon sale

B1:

basis

C1:

years held

D1:

= SUM(A1-B1)/(B1/C1)

Return on Investment

A1:

proceeds upon sale

B1:

original investment

C1:

=SUM(A1-B1)

Return on Rental Income

A1:

net profit

B1:

rental gross income

C1:

= SUM(A1/B1)

Rule of 69

A1:

=SUM(69/interest rate)+0.35

Rule of 72

A1:

=SUM(72/interest rate)

Rule of 113

A1:

=SUM(113/interest rate)

Semiannual Compounding

A1:

interest rate

B1:

=SUM(A1/2)

Simple Interest

A1:

principal

B1:

interest rate

C1:

=SUM(A1*B1)

Sinking Fund Payments

=FV(r, n, p,0,0)

where:

R

= periodic interest rate

N

= number of periods

0

= first zero is equal to PV, second zero is type and indicates payments made at the end of each period; change last zero to 1 for payments at the beginning of each period

Spread

A1:

sale price

B1:

asked price

C1:

=SUM(A1/B1)/B1

Straight-Line Depreciation

A1:

basis of asset

B1:

period (in years)

C1:

=SUM(A1/B1)

Tax Benefits from Reporting Losses

A1:

effective tax rate

B1:

net loss from real estate

C1:

=SUM(A1*B1)

Total Return

A1:

capital gains

B1:

total net income

C1:

net tax benefit (or cost)

D1:

years held

E1:

=SUM(A1+B1+C1)/D1

Trial Balance

A1:

total, asset account balances

B1:

total, liability account balances

C1:

net worth account balances

D1:

=SUM(A1-B1-C1)

A2:

revenue account balances

B2:

cost account balances

C2:

expense account balances

D2:

=SUM(A2-B2-C2)

C3:

=SUM(D1-D2) (check for zero balance)

Vacancy Rate

A1:

vacant units

B1:

total units

C1:

=SUM(A1/B1)

Volume of a Cylinder

A1:

3.1429

B1:

radius

C1:

height

D1:

=SUM(A1*(B1*B1)*C1)

Volume of a Rectangular Solid

A1:

length

B1:

width

C1:

height

D1:

= SUM(A1*B1*C1)

Weighted Average Interest Rate

A1:

loan balance #1

A2:

loan balance #2

A3:

=SUM(A1+A2)

B1:

interest rate, loan #1 (decimal form)

B2:

interest rate, loan #2 (decimal form)

C1:

=SUM(A1*B1)

C2:

=SUM(A2*B2)

C3:

=SUM(C1+C2)

D1:

= SUM(C3/A3)*100

Weighted Moving Average

Based on example of 12 values in the field:

A1

through A12: values of V1 . . . V12

B12:

number of values in the field

C12:

=SUM((A1:A12)+A12)/(B12+1)

Working Capital

A1:

current assets

B1:

current liabilities

C1:

=SUM(A1-N1)
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.59.187