# Mortgage

Shared by:
Categories
-
Stats
views:
17
posted:
11/6/2009
language:
English
pages:
2
Document Sample

```							Mortgage Schedule plus new APR (Apr 2000) calculation
General purpose loan/mortgage calculator and schedule with differing rates & fees every year and APR calculation Enter the required loan & annual interest rates in the tables below. Enter initial fees that are added to the loan. Also enter any initial costs, which are NOT added to the loan, including valuation & administration fees but less any immediate cashback, against year 0. Any compulsory monthly costs such as PP (Payment Protection) insurance is entered in the monthly fees column. The APR is the internal rate of return of the resultant cashflows taken to the term end. Calculate the APR by clicking on the "Calculate APR" button or pressing Control i. If an erroneous result occurs, change J32 (highlighted yellow) to a better guess. Use View, Zoom for best screen layout. See Loan Tab for monthly changes Change any blue figure. Use mouse to read comments Enter Initial Loan: £50,000 Interest-only Enter any fees added to loan: £0 Enter other initial fees not added to the loan in month 0 Actual advance: £50,000 Including added fees Enter Initial Term: 25 Years (max 35 yrs). Set interest rate each year in table below Enter Final Loan: £50,000 (0 for repayment, otherwise interest-only element) Compounding rests per annum: 12 Every year (eg 1 for annual, 12 for monthly, 365 for daily rests) APR = APR to be quoted = Total Charge for Credit - TCC = Total Amount Payable - TAP = 7.2511 % pa to four decimal places (same as the IRR for the full term) Rounded to one decimal, as required from 14th April 2000 As per the Consumer Credit Act THIS TABLE USED FOR APR CALCULATION ONLY Schedule with fixed rate, same normal mortgage payments, plus fees Year True fixed Total Theoretical Calculated interest % monthly year end end per month payment debt result 0 £49,900 1 0.5851 £125.00 £51,969 £0 2 0.5851 £208.33 £53,155 £0 3 0.5851 £208.33 £54,428 £0 4 0.5851 £312.50 £54,501 £0 5 0.5851 £312.50 £54,580 £0 6 0.5851 £312.50 £54,665 £0 7 0.5851 £312.50 £54,755 £0 8 0.5851 £312.50 £54,853 £0 9 0.5851 £333.33 £54,699 £0 10 0.5851 £333.33 £54,534 £0 11 0.5851 £333.33 £54,357 £0 12 0.5851 £333.33 £54,167 £0 13 0.5851 £333.33 £53,964 £0 14 0.5851 £333.33 £53,745 £0 15 0.5851 £333.33 £53,511 £0 16 0.5851 £333.33 £53,260 £0 17 0.5851 £333.33 £52,991 £0 18 0.5851 £333.33 £52,702 £0 19 0.5851 £333.33 £52,392 £0 20 0.5851 £333.33 £52,060 £0 21 0.5851 £333.33 £51,703 £0 22 0.5851 £333.33 £51,321 £0 23 0.5851 £333.33 £50,911 £0 24 0.5851 £333.33 £50,472 £0 25 0.5851 £333.33 £50,000 £50,000 26 0.5851 £0.00 £53,626 £0 27 0.5851 £0.00 £57,514 £0 28 0.5851 £0.00 £61,684 £0 29 0.5851 £0.00 £66,157 £0 30 0.5851 £0.00 £70,954 £0 31 0.5851 £0.00 £76,099 £0 32 0.5851 £0.00 £81,617 £0 33 0.5851 £0.00 £87,535 £0 34 0.5851 £0.00 £93,883 £0 35 0.5851 £0.00 £100,690 £0 This table assumes the same payments as the normal £50,000 schedule (plus costs) but with a fixed monthly interest rate. This rate is calculated with "Tools, Goal Seek" using a Script. The Script makes cell M67 = E19 by varying J32. M67 is the required end capital at the term end. The resultant interest is the monthly IRR which is converted to annual. Provided all the stautory costs & fees are entered, this IRR will equal the APR as laid down by the Consumer Credit Act 1974 (as amended to take effect in April 2000). Created by Michael Kelly e-mail mkelly@foxwood.demon.co.uk

7.3
£43,350 £93,350

Year

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

Nominal Interest % pa 3.000 5.000 5.000 7.500 7.500 7.500 7.500 7.500 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000 8.000
Total pmts Total Int

TCC = TAP =

Normal Annual Schedule Monthly Additional Additional mortgage costs costs payment per month per annum Initial: £100.00 £125.00 £0.00 £0.00 £208.33 £0.00 £0.00 £208.33 £0.00 £0.00 £312.50 £0.00 £0.00 £312.50 £0.00 £0.00 £312.50 £0.00 £0.00 £312.50 £0.00 £0.00 £312.50 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £333.33 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £93,250.00 £0.00 £100.00 £43,250.00 £43,350.00 £93,350.00

Debt at year end £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £50,000 £0 £0 £0 £0 £0 £0 £0 £0 £0 £0

Loan Schedule plus new APR (Apr 2000) calculation
General purpose loan calculator and schedule with differing rates & fees every month and APR calculation

Vers 1.3 21st July 2000

Enter the required loan and term in the first table below, including any initial fees that are added to the loan. In the second table enter either the monthly interest rates OR the known loan payment. Also enter any initial fees that are NOT added to the loan, including valuation & administration fees but less any immediate cashback) against month 0. Compulsory monthly costs such as PP (Payment Protection) insurance is entered in the monthly costs column. Check the schedule throughout. The APR is the internal rate of return of the resultant cashflows taken to the term end, calculated automatically with the IRR function. Unlike the mortgage calculation tab, this sheet illustrates every monthly transaction and the IRR calculation can be automatic. Use View, Zoom for best screen layout. Change any blue figure Enter Initial Loan: £5,000 Repayment Mortgage Enter any fees added to loan: £50 One-off initial fees only - regular monthly costs are added below Actual advance: £5,050 Including added fees Enter Initial Term: 36 Months (max 72 mths). Set interest rate each year in table below Enter Final Loan: £0 (0 for repayment, otherwise interest-only element) APR = APR to be quoted = Total Charge for Credit - TCC = Total Amount Payable - TAP = 13.4585 % pa to four decimal places (same as the IRR for the full term) Rounded to one decimal, as required from 14th April 2000 As per the Consumer Credit Act

13.5
£1,038 £6,038

Month

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

True Interest % pm 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000 1.000

Normal Monthly Schedule Calculated Actual Additional loan loan costs payment payment per month Initial: £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £167.73 £167.73 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00

Debt at month end £5,050.00 £4,932.77 £4,814.36 £4,694.77 £4,573.99 £4,452.00 £4,328.79 £4,204.34 £4,078.65 £3,951.71 £3,823.49 £3,693.99 £3,563.20 £3,431.10 £3,297.68 £3,162.92 £3,026.82 £2,889.36 £2,750.52 £2,610.29 £2,468.66 £2,325.62 £2,181.14 £2,035.22 £1,887.84 £1,738.99 £1,588.64 £1,436.80 £1,283.43 £1,128.54 £972.09 £814.08 £654.49 £493.30 £330.50 £166.07 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00

Cashflow for IRR/APR calc -£5,000.00 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £167.73 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00 £0.00

```
Related docs