SOLVED

spreadsheet to track mortgage interest paid and interest paid to borrow the funds to establish the m

Copper Contributor

HP all in one, Windows 10

 

I am borrowing funds to finance a private mortgage.  i want to track the interest paid on the loan for tax purposes, as well as track the interest charged to finance the mortgage as they are at 2 different rates.  the interested charged to finance will be on the depreciating balance of the mortgage.

 

any suggestions please?

13 Replies
best response confirmed by Richie970 (Copper Contributor)
Solution

@Richie970 

 

First suggestion: Please post a copy of whatever spreadsheet you've developed so far. 

 

This would include the various numbers to incorporate:

  • interest rate on the loan
  • interest rate on the mortgage
  • term of the loan
  • term of the mortgage (30 year? 15 year?)
  • payment schedules (monthly? quarterly)
  • amount of loan
  • amount of mortgage

Are you expecting both loan and mortgage payments to include principle AND interest, or will one or both be interest only? Do you want to allow for payments over and above the principle?

 

I gather you're the one who will be paying off the loan, and you are the one granting the mortgage... it's not necessary for you to reveal private or confidential data here, but it would help if your role is clear AND if some realm of reality is reflected in the numbers referenced above (i.e., you don't need to tell us the actual amounts of the loan/mortagage, but something that gives an idea of the ratio....and is somewhat "real world" like)

 

note there is no formulae as I am a relative novice with no formal training

@Richie970 

 

See if the attached makes sense. FWIW, I know my way around Excel, but am NOT a financial expert. The financial functions are available to you as they are to me. I tried to make sense of them here, and think the numbers look realistic, but would happily hear from others who may know them more fully.

 

I did make use of a few assumptions which you can change, in the tables there with a light blue background. The variables there are referenced by name in the formulas.

 

In the case of the Loan, I kept the loan balance as a steady 350K; in the case of the mortgage, the balance declines by the amount of principle paid each month.

@mathetes 

 

I n

made some adjustments keeping the mortgage payment the same each bi week as well as the payment to the loan.

do these make sense to you

@Richie970 

 

Since the mortgage is a private one, you and the payor can pretty much do whatever you like. To give added flexibility, I've added a column here to account for additional payments in case the payor chooses to pay extra in any given period.

 

And then, since you apparently want your payment of principle to the bank loaning you the money-- you want that principle payment to equal the amount paid on principle payment on the mortgage--I've modified the formula there to accomplish that. But also, because the interest rates and payment periods of the two loans are quite different--bi-weekly vs monthly--you can't just copy the balance column from the one to the other. In fact, since you're repaying the loan monthly and getting paid by the mortgage bi-weekly, you will need to pay a larger amount per month than the bi-weekly principle payment you receive. Mathematically, it would work out to 26/12 times the bi-weekly payment of $708.26. The resulting number is $1534.55.

 

Otherwise, I think those all look reasonable. You can see how changing the assumptions in the cells with blue backgrounds will cascade through the entire spreadsheet.

that looks good but since the loan is an PLC i can pay whatever I want , as long as it covers the interest for that month. finally how do I make the dates work, or just fill them in as I go along?

@Richie970 

 

Unless they're really going to be like clockwork, I'd do the dates as you go along.

 

Here's a good website for your learning. I've set if for some of the date-related functions, but it can help with lots of functions.

https://exceljet.net/formula/dynamic-date-list

 

i have a problem with the the formulas that you created to calculate the biweekly payment and to calculate how much was principle and how much was interest. the problem is that there is an annual interest rate of 2.3% but in Canada that interest is only calculated biannually. therefore the payment schedule you created for me is out by $.98 biweekly and I don't know how to correct that.

I am trying to calculate mortgage interest amounts for a mortgage I hold. The balanace is 54,953.18, 5.5%, monthly payments $656.
What is a formula for calculating monthly interest? How do I specify division? I have used balance x 5.5% divide by 365 days x number of days in month. I am not sure I have the division indicator correct. I get an error message that reads #NAME?

@ssmccayjunocom 

You wrote: 

What is a formula for calculating monthly interest? How do I specify division? I have used balance x 5.5% divide by 365 days x number of days in month. I am not sure I have the division indicator correct. I get an error message that reads #NAME?

 

It would help if you copied the actual formula.

For example, I'm interpreting what you've said to mean that your actual formula reads something like the following:

=A1*((.055/365)*B1

where A1 is the cell containing balance and B1 is the cell containing the number of days in the month

 

In any event, to answer your question about the "division indicator," it's the / character. So .055/365 is read by excel to mean ".055 divided by 365"

 

That said, maybe the most helpful thing with regard to calculating interest rate would be to refer you to the built-in function that performs that task. RATE  Here's an on-line reference that explains it in detail. https://exceljet.net/excel-functions/excel-rate-function

 

@mathetes  it was @ssmccayjunocom who wrote that question to you.  it wasn't me.  he seems to have found you from your replies to me earlier

 

but thank you again

@Richie970 

 

Indeed. And it was to @ssmccayjunocom that I had replied. The system just notifies anybody and everybody who had been part of the thread, even though, in this case, it was quite an old thread. I thought about recommending to him (her?) that a new thread be started, but it was easier to just reply. (No need to reply to this one). :)

 

1 best response

Accepted Solutions
best response confirmed by Richie970 (Copper Contributor)
Solution

@Richie970 

 

First suggestion: Please post a copy of whatever spreadsheet you've developed so far. 

 

This would include the various numbers to incorporate:

  • interest rate on the loan
  • interest rate on the mortgage
  • term of the loan
  • term of the mortgage (30 year? 15 year?)
  • payment schedules (monthly? quarterly)
  • amount of loan
  • amount of mortgage

Are you expecting both loan and mortgage payments to include principle AND interest, or will one or both be interest only? Do you want to allow for payments over and above the principle?

 

I gather you're the one who will be paying off the loan, and you are the one granting the mortgage... it's not necessary for you to reveal private or confidential data here, but it would help if your role is clear AND if some realm of reality is reflected in the numbers referenced above (i.e., you don't need to tell us the actual amounts of the loan/mortagage, but something that gives an idea of the ratio....and is somewhat "real world" like)

 

View solution in original post