Forum Discussion
spreadsheet to track mortgage interest paid and interest paid to borrow the funds to establish the m
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)
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.
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