If, in your example, only $5 is applied towards the capital, that's because the interest is calculated as $195. $5 is what remains after paying the interest.

Excel has a payment() formula; if I remember correctly, it tells you how much you must pay every month, so as to be paying a fixed amount every month, say, for 5 years. But in your case, the situation is different; it seems you want to pay different amounts every month, basically trying to pay as much as your means allow - and see how much you still owe.

I believe that calculating the interest, and applying any remainder to the capital, is the correct thing to do. - It would be convenient to check back with the bank after a while (every year, for example); there might be slight discrepancies in the calculation, which can accumulate over time.

>I beg to disagree. The bank has a formula of how they calculate the breakdown between principle and the interest. For example, if you pay $200 a month, they may apply only $5 dollars towards the principle and $195 to interest. And this - the breakdown - depends on the term of the loan (and interest of course). That is, the number of years or months they use. So by changing the term - say from 20 years to 10 years - you pay more towards the principle (in every payment) and less to interest.
>
>But so far when I look at the numbers of the loan I am investigating (my daughter's) I can't figure out the term. It looks like 11 or 12 years. But this is an odd number.
>
>Thank you.
>
>>I think you don't need to make such an assumption. It is quite possible to agree a minimum payment of, say, $200 per month, but to pay more as you get money. This will reduce your capital, and the interest due, since the bank is supposed to calculate the interest on whatever you still owe them.
>>
>>>First, thank you for your input. I think the key - that I was missing - is that bank calculates every amount based on the term (although I am not sure). That is, they assume that the loan has to be paid in 25 or 30 or whatever number of years. If I have this number (years to pay) than excel has formulas for calculating.
>>>
>>>>Well, I would guess the bank calculates the interest first (on the basis of how much you still owe), and whatever is left is assigned to principal (not principle). So, you would just need to set up the following columns:
>>>>A: Date
>>>>B: Amount owed (formula subtracts the previous amount owed minus the principal paid)
>>>>C: Amount paid
>>>>D: How much of that is interest (use compound interest formulae)
>>>>E: The principal paid (whatever is left after paying the interest) (can be negative if you pay too little - in which case your debt increases)
>>>>
>>>>I assume 5% interest is per annum; this corresponds to a factor of 1.05. The monthly interest rate in this case is 1.05^(1/12) - 1. This is close to 5% / 12, but not exactly so.
>>>>
>>>>>I need to find a formula(s) in MS Excel that can be used to calculate how a student loan payments are broken down between principle and interest.
>>>>>
>>>>>For example, say a student borrows $100,000 at the rate of 5%. Then the student pays different amount each month (according to his/her finances). For each payment I am trying to find how much the bank allocates to principle and how much to interest. For example, if you I could create the following spreadsheet
>>>>>
>>>>>

>>>>>DATE         PAYMENT AMOUNT     PRINCIPLE     INTEREST
>>>>>01/01/14    $500.                               ??                     ??
>>>>>02/04/14    $700.                               ??                     ??
>>>>>03/10/14    $650.                               ??                     ??
>>>>>and so on.
>>>>>

>>>>>
>>>>>Does Excel have formulas for the PRINCIPLE and INTEREST columns?
>>>>>
>>>>>TIA