Purchase Solution

Excel accounting formulas

Not what you're looking for?

Ask Custom Question

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
Purchase this Solution
Solution provided by:
Gaurav Agarwal, MS
Education
  • BE, Bangalore University, India
  • MS, University of Wisconsin-Madison
Recent Feedback
  • "Your explanation to the answers were very helpful."
  • "What does 1 and 0 means in the repair column?"
  • "Went through all of the formulas, excellent work! This really helped me!"
  • "try others as well please"
  • "Thank you, this helped a lot. I was not sure how to plug in those numbers to a formula. This was a great help. Now I have to figure out how to explain cost of capital is used in net present value analysis, and how cost of capital is used in net present value analysis. This stuff gets confusing."
Purchase this Solution
Free BrainMass Quizzes
Marketing Research and Forecasting

The following quiz will assess your ability to identify steps in the marketing research process. Understanding this information will provide fundamental knowledge related to marketing research.

Learning Lean

This quiz will help you understand the basic concepts of Lean.

Accounting: Statement of Cash flows

This quiz tests your knowledge of the components of the statements of cash flows and the methods used to determine cash flows.

Situational Leadership

This quiz will help you better understand Situational Leadership and its theories.

MS Word 2010-Tricky Features

These questions are based on features of the previous word versions that were easy to figure out, but now seem more hidden to me.

View More Free Quizzes
Related BrainMass Solutions

  • Formulas for Bookkeeping in Excel

    A 26-page Accounting excel sheet is filled in with the necessary formulas.

  • Use Excel to track grades and calculate GPA

    Excel spreadsheet to get you started tracking your grades in excel. You can view formulas and replicate them practicing and learning excel.

  • Responsibilities of an Accounting Clerk

    How to use Excel? Excel basic spreadsheet knowledge such as formatting and basic formulas required? All these are the responsibilities and duties of an Accounting Clerk I.

  • Creating an income statement and balance sheet

    Attached is a dynamic Excel file with the suggested solution. You will see all formulas and relevant links inside the calculated fields.

  • Excel Payroll Spreadsheet

    304062 Implement a payroll accounting spreadsheet in Excel. You will enter in the correct Formulas or Functions needed to find out the monthly gross income for a Computer Repair Business.

  • Variance analysis

    84418 Accounting (See attached file for full problem description) Hello Please see the attached excel file for all the details. The calculations are done using reference cells and formulas. Hope this helps you.

  • Managerial Accounting: Activity-and Strategic-Based Responsi

    I would like thorough and detailed written explanations with formulas in an Excel spreadsheet in how you arrived at each of your answers in the "Required:" section. Please see the attached file.

  • Payroll Accounting: Ryan Company

    See excel for schedule. Click into cells to see the formulas. The Solution using Ryan Company as an example of salary payments, payroll expenses and tax remittance. See excel for schedule. Click into cells to see the formulas.

  • Accounting Inventory problem

    Your response is in excel and uses formulas to compute the responses so you have a template for other problems. Your response is in excel and uses formulas to compute the responses so you have a template for other problems.

View More Related Solutions