VBA代写 PROJECT 2 – MORTGAGE CALCULATOR

PROJECT 2 – MORTGAGE CALCULATOR

Due Date: Midnight, Friday 21 September 2018 Weighting: 30%
Type: Individual
Submission: Electronic Submission via AUT Online

Instructions for the Project

Students are required to build a mortgage calculator in Excel 2016. The calculator should have the following features:

  • It should be capable of calculating the maximum amount a home-buyer can borrow, based on the user’s choice of preferred output. The appropriate inputs should include but not limited to: family incomes and assets, living expenses and debts, and regulations on deposit of residential property loan.
  • It should allow users to calculate the value of the outputs based on one of three different types of mortgage

    o Normal Mortgage
    o Growing Annuity Mortgage (payments grow at a constant rate over

    the life of the mortgage aka a growing annuity)
    o Interest Only Mortgage (payments cover only the interest

    component until the final payment which covers the entire principle

    of the mortgage)

  • The calculator should be capable of dealing with payments occurring over

    varying payment frequencies

  • The calculator should contain an amortisation table with at a minimum the

    following columns (payment, interest component of the payment, principle

    component of the payment and principle balance)

  • The calculator should be capable of dealing with items such as loan

    processing fee/low equity margin.

  • The calculator should contain a graph breaking down payments
  • The calculator should follow the rules of good financial model design.

    Students are expected to be able to explain or demonstrate any features or techniques in their assignments if requested.

    Marking Criteria

    To receive a C Grade, students must ensure their calculator meets the following requirements:

Calculation

  • Calculator must correctly calculate the maximum amount of loan and the per period and annual interest rates for a table mortgage
  • Calculator must have a graph that correctly shows the breakdown of interest and principle
  • Calculator must include an accurate loan amortisation table that updates for changes in inputs

Layout

• Calculator conforms to the following rules of good design o Clear separation of inputs and outputs
o Formulas are correct and correctly linked

Excel Features

• Calculator will at a minimum include suitable Excel control features i.e. radio boxes, combo boxes etc

To receive a B Grade, students must ensure their calculator meets the following requirements:

Calculation

  • Calculator must correctly calculate the maximum amount of loan and the per period and annual interest rates for a table mortgage and either an interest only or a growing mortgage
  • Calculator must possess a graph that correctly shows the breakdown of interest and principle in an easy to understand format
  • Calculator must include an accurate loan amortisation table that updates for changes in inputs and only displays values for the mortgage period

Layout

• Calculator conforms to the following rules of good design o Clearseparationofinputsandoutputs
o Formulasarecorrectandcorrectlylinked
o The interface is intuitive and easy to use

Excel Features

• Calculator will at a minimum include:
o SuitableExcelControlfeatures(i.e.radioboxes,comboboxes

etc)
o Conditional formatting
o ModelemploysVBAprogrammingtoasmalldegree o Model is tidy and easy to navigate

To receive an A Grade, students must ensure their calculator meets the following requirements:

Calculation

  • Calculator must correctly calculate the maximum amount of loan and the per period and annual interest rates for a table mortgage and either an interest and a growing mortgage
  • Calculator must have a suitable graph type that correctly shows the breakdown of interest and principle in an easy to understand format. In addition the graph should update for changes in the mortgage period and payment frequency
  • Calculator must include an accurate loan amortisation table that updates for changes in inputs and only displays values for the mortgage period

Layout

• Calculator conforms to the following rules of good design o Clearseparationofinputsandoutputs
o Formulasarecorrectandcorrectlylinked
o Theinterfaceisintuitiveandeasytouse

o SuitableSummariesareprovided

Excel Features

• Calculator will at a minimum include:
o Suitable Excel Control features (i.e. radio boxes, combo boxes

etc)
o Conditionalformatting
o Datavalidation
o Customformatting
o Namedranges.
o ModelshoulduseconsiderableVBAprogramming o Modelistidyandeasytonavigate