Share
Explore BrainMass

Excel accounting formulas

I need to build an Excel spreadsheet for the calculations in the chart. The spreadsheet must include Excel formulas used (I have listed the Excel formulas- I am having a hard time plugging them in to the spreadsheet). I would also like the detailed formulas written out so that I can plug them in for later practice and understand how I got to this point. During this exercise, I am trying to figure out the Net per Value and the IRR and payback. The initial investment, depreciation, and cash flow years 1-5 are results of the data. I also need to know how to set up these areas (including any formulas) in Excel to produce the same results and apply to different problems.

The results should be plugged into Excel. The formulas showing how the answers achieved are what I need. The formulas must be written out on the Excel spreadsheet. The formulas must calculate to the results that are attached

Excel Formulas that I have:

 Future Value
The value of a sum of money in the future (FV) given a sum of money invested today (PV) compounded at a per period rate of 'k' for 'n' (nper) periods.
=FV(rate,nper,pmt,PV,type)

 Present Value
The value of a sum of money today (PV) given a sum of money in the future (FV) discounted at a per period rate of 'k' for 'n' (nper) periods.
=PV(rate,nper,pmt,FV,type)

 Future Value of Annuity: Future Value of an Annuity
Annuity = A level payment for a certain number of periods.
(Normally payments take place at the end of the period, type 0)
Given an annuity (pmt), a number of periods (n or nper) and a per period interest rate of 'k', what is the value at the end of the annuity period (FVA).
=FV(rate,nper,pmt,PV,type)

 Present Value of an Annuity
Annuity = A level payment for a certain number of periods.
(Normally payments take place at the end of the period, type 0)
Given an annuity (pmt), a number of periods (n or nper) and a per period interest rate of 'k', what is the value at the beginning of the annuity period (PVA). This resembles a LOAN or an INVESTMENT.

=PV(rate,nper,pmt,FV,type)

 Present Value of a Perpetuity
Perpetuity = A level payment forever.
Given a perpetuity and a per period interest rate of 'k', what is the value at the beginning of the period (PVP).
PVP = Cash Flow / Rate

 Interest Rates
n = Total number of periods
m = Number of periods per year
k = Per period interest rate
I = Annual uncompounded interest rate
APR = Annual compounded interest rate
k = I / m
I = k x m
APR = [ (1 + I/m) ^ m ] - 1

 Uneven Cash Flows - NPV

= -CFo + NPV(rate,range)

 Uneven Cash Flows - IRR

= IRR(range,guess)

 Depreciation
Straight Line
Cost less Salvage Value = Depreciable Base
Depreciable Base / Years of Life = Depreciation

Attachments

$2.19