the years are in Row 1. 2020, 2021, etc. The user is planning to purchase a house, but doesn't know what year. The data rows include fair market value, outstanding mortgage, equity, interest paid during the year and the tax value of the interest deduction. The mortgage is a function of what year and also the purchase price which grows over time, so if he buys in 2025, he'll pay more and have a larger mortgage than if he purchases in 2023. I know the purchase price, outstanding mortgage, and mortgage paymens over the various possible years. My current practice is to have the amortization schedule on another worksheet and simply refer to it (lookup the outstanding balance every year and the sum of the interest payments). I then clumsily insert/delete cells to move the block of data left and right to get the purchase year correct. But that generates errors in cells below that reference the data block. I can redraw those cells with errors, but it is awkward. I thought of using a lookup function, but cell C5 (for example) sometimes refers to data from year 3 and sometimes to year 4, etc., depending on when the buyer plans to purchase the house. Is any of this making sense?

i dont really under stand the calc but i think it should be possible to make it dynamic.


Can you attach an example workbook for me to play with?

I have been moving rows 22-34 left and right by inserting/deleting cells, but you'll see that throws errors in rows 48 and 55. I can solve that by redrawing rows 36-57 after I move the rows dealing with the apartment purchase. Cells B16-B20 are hard input numbers. The rest of rows 18-20 are just references for me to show what the value and expenses will be as the years pass for the target apartment. Those numbers are not involved in the calculations. As you will see, rows 26, 30 refer to the amortization schedule on a different worksheet. I can't calculate these numbers easily. The mortgage reduction is not linear as one pays less interest as the years go by. As a side note, the equity number in line 27 is not a true sum. I included the possibility that I would relocate to an area where housing costs are cheaper. The adjustment is in the equity number even though the FMV and mortgage would be lower in that scenario. Have fun.

Attached Files

calculator.xlsx (117.4 KB, 7 views)

To try and break it down into manageble chunks


I have highlighted what i think are the varibles that you would type in, in blue.


and pretty much everything else will calculate from that? for example i would think that b1 should always be the current year or maybe it should equal the move out date?


Ideally if we can get the years to be dynamic the calculation will work from them and we can hide what we dont want.


In my mind nothing should move as each calc can look for the correct detail


It might be a slow approch but we can get a decent spready at the end of this

Attached Files

calculator_1.xlsx (117.0 KB, 8 views)

Thanks for the effort. My thoughts:

1. rows 1-6 are irrelevant to the calculations. No need to move them into the blue section like you did.
2. You have shifted rows 24-27 to the right. Those cells need to align with rows 22, 30-34.
3. I didn't see any changes to the formulas in rows 26, 30 which are my problems. I can't calculate the values in those rows. I need to refer to the other worksheet.
4. You think this was a lot of numbers? I deleted 3/4 of the worksheet before I sent it to you. Look at rows 36-48. All those go into the year's income/expenses. Look at rows 52-55. those are the components of net worth. Calculating the mortgage is just one expense. apartment equity is one component of net worth.

Okay will forget about 1 t 6 for the time being


Based on t he attached example what column do you think rows 22 to 34 should start?

cell B16 says the year of apt. purchase. rows 22-34 should align in that year, in this case 2027, or column J. I can't remember if B16 is connected programatically anywhere. I don't think it it.