Loan Repayment Calculator

£35,000 is lent and returned over 1 year in identical montly instalments at an interest of 6%. Excel PMT() is used in Excel table row C7 to determine the amount of the repayment. PMT (interest rates, number of periods, capital, FutureValue, PaymentsDue). Interest should be charged on a sole repayment date and not on the yearly interest as such.

Excel displays payoffs as minus by default. In the case of a loan repayable over a longer term, the interest rates may vary and the amount repayable each month may be adjusted to the new interest year. The calculation of a partial interest variation over periods can be done in different ways, and I do not know if there is a standardised method that has been adopted by all bankers.

Excel CUMPRINC can only handle whole monthly (or period) values and handles each periodic value as a whole number. The February is in 10 cycle of the loan. You must make a seperate computation for February (if the exchange rates change partially during the month) and then for the following monthly dates.

Then, for calculation purpose, the outstanding loan amount can be handled as a new loan, repayable in the same amount over the remainder of the number of years. The number of tariffs is calculated on the basis of the number of workingdays. Then the new 5% percentage (=0. 417%) is valid for the entire month of March and April.

With two new features, you can compute the interest and capital amount for any given loan timeframe (or period). Total interest and capital corresponds to the PMT () profit. Feature CUMPRINC computes the amount of the capital repayment between a certain timeframe.

CCUMPRINC (interest rates, number of cycles, sold-to party, initial cycle, end cycle, incoming payments). Likewise, the CUMIPMT feature will return the interest payments made between a certain date and time. CUMIPMT( interest rates, number of cycles, sold-to party, initial cycle, final cycle, incoming payments). The number of Feb - Apr spells will be 3 and the starting and ending spells will be 1 in February, as the interest rates only apply to this one.

Customer is the remainder outstanding amount (£8,947) and not the initial £35,000. For this example, the value of'0' indicates the end of the cycle. Crucial to such agreements is the incidence or timing of interest calculation.