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
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham