Calculate Interest - Excel by Ben_Longjas

VIEWS: 5,160 PAGES: 8

									USING A SPREADSHEET TO CALCULATE EFFECTIVE INTE

1a) Interest Based on Original Loan Amount

INPUTS

Original Loan Amount                               PV                  USD    100

Fees and Commission                                PV                  USD     0

Loan Period                                         n              Months      3

Nominal Interest Rate                               i         % per month      2


OUTPUTS

Monthly Payment

Monthly Payment - Principal                                            USD   -33.33

Monthly Payment - Interest                                             USD    -2.00

Monthly Payment - Total                                                USD   -35.33


Total Amount Paid

Total Paid (Principal plus Interest)                                         -106.00

Total Paid (Interest only)                                                    -6.00



                                             Effective Interest Rate         2.97%




1b) Interest Based on Declining Balance

INPUTS

Original Loan Amount                               PV                  USD    100

Fees and Commission                                PV                  USD     0

Loan Period                                         n              Months      3

Nominal Interest Rate                               i         % per month      2
OUTPUTS

Monthly Payment

Monthly Payment - Total                                                  USD   -34.68


Total Amount Paid

Total Paid (Principal plus Interest)                                     USD   -104.03

Total Paid (Interest only)                                                      -4.03



                                               Effective Interest Rate         2.00%




2) Interest Deducted Up Front from Original Loan Amount

INPUTS

Total Loan Amount                                    PV                  USD    100

Loan Period                                           n              Months      3

Nominal Interest Rate                                 i         % per month      2


OUTPUTS

Amortised Monthly Payment

Amortised Monthly Payment - Principal                                    USD   -32.68

Amortised Monthly Payment - Interest                                     USD    -2.00

Amortised Monthly Payment - Total                                        USD   -34.68


Actual Monthly Payment

Actual Monthly Payment (Principal Only)                                  USD   -33.33

Amount Paid in Interest                                                  USD    -4.03

Original Loan less Interest Payment                                      USD   95.97
                                                           Effective Interest Rate         2.08%




3) Loans with Compensating Balance

INPUTS

Total Loan Amount (including fees)                               PV                  USD    100

Loan Period                                                       n              Months      3

Nominal Interest Rate                                             i         % per month      2

Amount Required to be kept on Deposit                                                USD    25


OUTPUTS

Amount Received by Borrower

Amount Received                                                                      USD    75


Amortised Monthly Payment

Amortised Monthly Payment - Principal                                                USD   -32.68

Amortised Monthly Payment - Interest                                                 USD   -2.00

Amortised Monthly Payment - Total                                                    USD   -34.68


Effective Monthly Payment

Income Forfeited                                                                     USD   -0.50

Effective Monthly Payment                                                            USD   -35.18


Payment Schedule

Amount Received                                                                              75
Payment Month 1                                                                            -35.18
Payment Month 2                                                                            -35.18
Payment Month 3 (Final Month - when deposit is returned)                                   -10.18
Internal Rate of Return <=> Effective Interest Rate   4.32%
EFFECTIVE INTEREST RATES



     Please enter figure here

     Please enter figure here

     Please enter figure here

     Please enter figure here




     Please enter figure here

     Please enter figure here

     Please enter figure here

     Please enter figure here
Please enter figure here

Please enter figure here

Please enter figure here
Please enter figure here

Please enter figure here

Please enter figure here

Please enter figure here

								
To top