An Excel Function eludes me - Can you please help?

[bribin]

Greetings
Alas I dont know my PMT's from my FV's in Excel.
I seek an Excel Funtion Name and would be most oblidged if someone could identify it.

The formula notation; to determine
a Settlement figure, is

I know the 2 Functions above are the same with differing inputs
but what is the Name of this Excel Function?

Thanking you in anticipation,
Regards

 

[James006]

Hello,

When it comes to Financial functions ... Excel has plenty of resources ...!!!

Could you clarify your question ...???

Do you have a series of cash-flows ... or only future values ...???

What is your expected result ...???

 

[bribin]

Hello,

When it comes to Financial functions ... Excel has plenty of resources ...!!!

Could you clarify your question ...???

Do you have a series of cash-flows ... or only future values ...???

What is your expected result ...???

Sorry James

 

[bribin]

Name the Excel Function

A loan of £5,000 is repayable by 48 monthly instalments of £134.57, starting one month after the relevant date. The monthly repayments include interest and all other charges included in the total charge for credit. Thus the total amount repayable = £134.57 x 48 = £6,459.36. The total charge for credit = £6,459.36 −£5,000 = £1,459.36.

The creditor receives notice from the debtor requesting early settlement immediately after payment of the 12th instalment (i.e. after one year).

Assuming that no charges are excluded from the calculation of the rebate under regulation 3(2), the APR on the loan required for the calculation of the rebate is 14% per annum.

The creditor opts to calculate the rebate using periods of one month, giving a period rate equivalent of the APR = (1.14(1/12)−1) x 100 = 1.0979% per month. Hence, for the purposes of the formula in regulation 4(1)—

A1 = 5,000

B1 = 134.57 = B2 = ... ... ... ... = B48

r = 1.0979/100 = 0.010979

m = 1

n = 12

a1 = 12 (working in periods of 1 month)

b1 = 11

b2 = 10

b3 = 9

:

b11 = 1

b12 = 0

Then the loan outstanding immediately after payment of the12th instalment as calculated by the formula in regulation 4(1) is—

5,000 x (1.010979)12 − (134.57 x 1.01097911 + 134.57 x 1.01097910 + ... ... ... ... + 134.57 x 1.0109791 + 134.57 x 1.0109790)

= 5,700.01 − (151.74 + 150.10 + 148.47 + 146.85 + 145.26 + 143.68 + 142.12 + 140.58 + 139.05 + 137.54 + 136.05 + 134.57)

= 5,700.01 − 1,716.01 = £3,984.00.