Spreadsheet to track contract value

[Maryrachel713]

I know someone has already done this with far more success…does anyone have a spreadsheet (or just the headings) that I might use to track my contract. Not on what I want but something along the lines of cost paid, nights used, points per night.

Ideas?

 

[CSLucas]

I have one (but I stopped updating it once we decided to just switch over to direct since at that point it was no longer a rational break-even decision as much as a "we just want this" decision)
Mine has 4 tabs:
Tab 1: Point $$ Per Year
Initial contract cost is spread out over years on contract to get the base price/point/year
Annual fees/point are added to the base price every year to get total cost to me of that point in each year
***You need this tab because if you have bookings using banked or borrowed points, the cost/point is different each year and you will need to account for that
Tab 2: Total Cost of Ownership
Includes Purchase price plus annual dues each year as a running total
Has a cell for Cost Less Savings (savings comes from next tab)
Tab 3: Cash Price of Bookings
Column headings for: Trip Date, Cash Value of Room, # Points Used, Point Cost, Savings
**point cost comes from tab #1 times the number of points used (make sure you are taking into account which UY your points are coming from), savings is obviously cash cost less point cost and this is the number carried to Tab 2 to work out cost of ownership less savings
Tab 4: Breakeven Analysis
Total Cost to Date
LESS: Total Savings to Date
----------------------
Sum/# of Points = Breakeven Price/Point if I were to sell

My chart assumes we would be staying in the same room whether or not we were DVC members, which is true for our family. If you would normally stay somewhere else, you would need to change the cash cost for booking to match wherever you would stay instead of the DVC room cost.

 

[Maryrachel713]

I have one (but I stopped updating it once we decided to just switch over to direct since at that point it was no longer a rational break-even decision as much as a "we just want this" decision)
Mine has 4 tabs:
Tab 1: Point $$ Per Year
Initial contract cost is spread out over years on contract to get the base price/point/year
Annual fees/point are added to the base price every year to get total cost to me of that point in each year
***You need this tab because if you have bookings using banked or borrowed points, the cost/point is different each year and you will need to account for that
Tab 2: Total Cost of Ownership
Includes Purchase price plus annual dues each year as a running total
Has a cell for Cost Less Savings (savings comes from next tab)
Tab 3: Cash Price of Bookings
Column headings for: Trip Date, Cash Value of Room, # Points Used, Point Cost, Savings
**point cost comes from tab #1 times the number of points used (make sure you are taking into account which UY your points are coming from), savings is obviously cash cost less point cost and this is the number carried to Tab 2 to work out cost of ownership less savings
Tab 4: Breakeven Analysis
Total Cost to Date
LESS: Total Savings to Date
----------------------
Sum/# of Points = Breakeven Price/Point if I were to sell

My chart assumes we would be staying in the same room whether or not we were DVC members, which is true for our family. If you would normally stay somewhere else, you would need to change the cash cost for booking to match wherever you would stay instead of the DVC room cost.

This is what I was thinking but I am definitely not sophisticated enough to make that myself! Great stats and I'll try to do as much of that as I'm able.

 

[perchy]

I’ve been keeping track of my total actual costs, total number of nights, and running cost per night.

I have to look, but I have tabs for contracts, points, running total costs including dues, stays (number of nights) and trips (contracts and points used per trip).

When my running costs divided by total number nights stayed drops below what I’d typically pay for a hotel room, I’ll feel justified in my insanity.

I focus less on time value of money and just look a current day cost.

 

[beldred]

I have one (but I stopped updating it once we decided to just switch over to direct since at that point it was no longer a rational break-even decision as much as a "we just want this" decision)
Mine has 4 tabs:
Tab 1: Point $$ Per Year
Initial contract cost is spread out over years on contract to get the base price/point/year
Annual fees/point are added to the base price every year to get total cost to me of that point in each year
***You need this tab because if you have bookings using banked or borrowed points, the cost/point is different each year and you will need to account for that
Tab 2: Total Cost of Ownership
Includes Purchase price plus annual dues each year as a running total
Has a cell for Cost Less Savings (savings comes from next tab)
Tab 3: Cash Price of Bookings
Column headings for: Trip Date, Cash Value of Room, # Points Used, Point Cost, Savings
**point cost comes from tab #1 times the number of points used (make sure you are taking into account which UY your points are coming from), savings is obviously cash cost less point cost and this is the number carried to Tab 2 to work out cost of ownership less savings
Tab 4: Breakeven Analysis
Total Cost to Date
LESS: Total Savings to Date
----------------------
Sum/# of Points = Breakeven Price/Point if I were to sell

My chart assumes we would be staying in the same room whether or not we were DVC members, which is true for our family. If you would normally stay somewhere else, you would need to change the cash cost for booking to match wherever you would stay instead of the DVC room cost.

This sounds really great and something I should start doing. I did a quick calculation on my Disney planning spreadsheet for this years trip to figure out what our hotel stay "cost" us as part of my budgeting. But setting this up properly would make it easier for all the years I have to come. (We just bought in November and just completed our first stay!)
I'm a spreadsheet nerd but am very visual. Any chance you'd be willing to share a google sheets version (stripped of your personal info of course) or at least a screenshot of your tabs?

 

[CSLucas]

This sounds really great and something I should start doing. I did a quick calculation on my Disney planning spreadsheet for this years trip to figure out what our hotel stay "cost" us as part of my budgeting. But setting this up properly would make it easier for all the years I have to come. (We just bought in November and just completed our first stay!)
I'm a spreadsheet nerd but am very visual. Any chance you'd be willing to share a google sheets version (stripped of your personal info of course) or at least a screenshot of your tabs?

I'd be happy to send it to you!

 

[ak517]

I also keep track of rack rate + tax for value (ASMu) and moderate (CSR) rooms for the same dates (without discounts, knowing they’d be maybe 10-15% less if there was a discount available). This is just for comparison sake, and really started because my non-DVC friend asked to see them. For nearly ever stay, the price of a value resort has been within a few percent of my lifetime $/point cost (I was a little worried when I first included this comparison haha). I don’t consider the time value of money in my estimates, but you can if you’re fancy.

 

[Maryrachel713]

I have one (but I stopped updating it once we decided to just switch over to direct since at that point it was no longer a rational break-even decision as much as a "we just want this" decision)
Mine has 4 tabs:
Tab 1: Point $$ Per Year
Initial contract cost is spread out over years on contract to get the base price/point/year
Annual fees/point are added to the base price every year to get total cost to me of that point in each year
***You need this tab because if you have bookings using banked or borrowed points, the cost/point is different each year and you will need to account for that
Tab 2: Total Cost of Ownership
Includes Purchase price plus annual dues each year as a running total
Has a cell for Cost Less Savings (savings comes from next tab)
Tab 3: Cash Price of Bookings
Column headings for: Trip Date, Cash Value of Room, # Points Used, Point Cost, Savings
**point cost comes from tab #1 times the number of points used (make sure you are taking into account which UY your points are coming from), savings is obviously cash cost less point cost and this is the number carried to Tab 2 to work out cost of ownership less savings
Tab 4: Breakeven Analysis
Total Cost to Date
LESS: Total Savings to Date
----------------------
Sum/# of Points = Breakeven Price/Point if I were to sell

My chart assumes we would be staying in the same room whether or not we were DVC members, which is true for our family. If you would normally stay somewhere else, you would need to change the cash cost for booking to match wherever you would stay instead of the DVC room cost.

Would you be able to send me a stripped version as well?

 

[JPKanks]

Make sure you add about 4% each year on your annual dues cost. Also, in addition to using cash rack rate, use renting DVC for that analysis too. I think right now you can rent some for $19 a point with board sponsor. I know a lot of folks say to use cash rate, but there is usually always a deal or way to save. Good luck.

 

[Wendysofftoneverland]

I have one (but I stopped updating it once we decided to just switch over to direct since at that point it was no longer a rational break-even decision as much as a "we just want this" decision)
Mine has 4 tabs:
Tab 1: Point $$ Per Year
Initial contract cost is spread out over years on contract to get the base price/point/year
Annual fees/point are added to the base price every year to get total cost to me of that point in each year
***You need this tab because if you have bookings using banked or borrowed points, the cost/point is different each year and you will need to account for that
Tab 2: Total Cost of Ownership
Includes Purchase price plus annual dues each year as a running total
Has a cell for Cost Less Savings (savings comes from next tab)
Tab 3: Cash Price of Bookings
Column headings for: Trip Date, Cash Value of Room, # Points Used, Point Cost, Savings
**point cost comes from tab #1 times the number of points used (make sure you are taking into account which UY your points are coming from), savings is obviously cash cost less point cost and this is the number carried to Tab 2 to work out cost of ownership less savings
Tab 4: Breakeven Analysis
Total Cost to Date
LESS: Total Savings to Date
----------------------
Sum/# of Points = Breakeven Price/Point if I were to sell

My chart assumes we would be staying in the same room whether or not we were DVC members, which is true for our family. If you would normally stay somewhere else, you would need to change the cash cost for booking to match wherever you would stay instead of the DVC room cost.

I do tab 1, but then next I use that info to calculate cost per stay, paying attention to which contract/year's points were used (since as you noted, the cost/pt will be different with borrowed/banked points, plus I have 3 contracts, 2 resorts). I divide that by number of nights to get cost per night. Then I have a running list of most to least expensive stays (by nightly average rate), since I know I wouldn't be staying in the same room without DVC. Though perhaps I should compare to current year data as opposed to past stays. FWIW, my most expensive stays were AoA, POR, and CBR, all more expensive than any DVC trips so far (though we've only stayed in studios, with the exception of RCI trades into 1 bedrooms).

 

[tom1944]

My cost analysis is fairly easy. I wanted to buy DVC in 1996 but I would have sacrificed a significant number of things to pay for it.

I wanted to buy it in 2023 and I had the money for the purchase and to pay for the trips without any real burden.

I bought it and plan to enjoy it for as ever long as I can.

 

[heynowirv]

My cost analysis is fairly easy. I wanted to buy DVC in 1996 but I would have sacrificed a significant number of things to pay for it.

I wanted to buy it in 2023 and I had the money for the purchase and to pay for the trips without any real burden.

I bought it and plan to enjoy it for as ever long as I can.

That was ours as well in 2006.,,,,,And now that we have grand princesses we enjoy it even more.

 

[smrice95]

I don't do a spreadsheet. I have a running record. I started with what the contracted costed. Every year I add the price of dues and when I book a reservation, I subtract what it would cost me if I was paying cash. I choose to do the same booking and not where we would be staying if I did not own DVC. The answer may be home! I do take into account any discounts at the time and taxes. When I added a 2nd contract I added that on. When you get to a negative number you have made your money (yes I know interested etc, but I am not into figuring all that out.)

 

[WeIcomeHome]

I put together a Google sheet to track cost and room savings over time and calculate the break even point with or without opportunity cost accounted for.

I tried posting a link to the sheet but disboards flags it as spam so I can't post it. If anyone is interested in the sheet let me know. Not sure if the issue is because I'm new to the forum or just a general issue linking Google sheets.

 

[gisele2]

I put together a Google sheet to track cost and room savings over time and calculate the break even point with or without opportunity cost accounted for.

I tried posting a link to the sheet but disboards flags it as spam so I can't post it. If anyone is interested in the sheet let me know. Not sure if the issue is because I'm new to the forum or just a general issue linking Google sheets.

I am interested, can you PM it ? Thanks

 

[WeIcomeHome]

Looks like I need a few more posts before I can pm. May be the reason I couldn't post links too?

 

[WeIcomeHome]

Looks like I can post the link now for the google sheet to calculate a break even point with or without opportunity cost. The spreadsheet is protected but allows all users to edit the required inputs. If you would like to take a look it is available here:

DVC Break Even Calculator

Some notes about the spreadsheet:
1. Please download the file to your computer or save it to your personal drive if you want to make changes to the inputs. Anyone with the link can edit the inputs so your information will be lost if you don't.
2. If you find any errors in the calculations or if you want an unprotected version to play with let me know.
3. The opportunity cost calculation is a rough estimate as it calculates an average return on investment at the end of the year after maintenance fee cost, interest payments and savings over rack rates/rental rates are accounted for.
4. I left it up to the user to determine how they wanted to determine the cost of the stay without DVC whether that is the cash rate or point rental rate.
5. Current input values assume 3% increase per year in both the MF and rack rate for rooms although these values would be replaced by your actual information over time.

 

[E2ME2]

Looks like I can post the link now for the google sheet to calculate a break even point with or without opportunity cost. The spreadsheet is protected but allows all users to edit the required inputs. If you would like to take a look it is available here:

DVC Break Even Calculator

Some notes about the spreadsheet:
1. Please download the file to your computer or save it to your personal drive if you want to make changes to the inputs. Anyone with the link can edit the inputs so your information will be lost if you don't.
2. If you find any errors in the calculations or if you want an unprotected version to play with let me know.
3. The opportunity cost calculation is a rough estimate as it calculates an average return on investment at the end of the year after maintenance fee cost, interest payments and savings over rack rates/rental rates are accounted for.
4. I left it up to the user to determine how they wanted to determine the cost of the stay without DVC whether that is the cash rate or point rental rate.
5. Current input values assume 3% increase per year in both the MF and rack rate for rooms although these values would be replaced by your actual information over time.

I have run similar analyses, and get similar results. I like the >$500K figure you show as potential savings after 50 years! That's based on the 200 points you input. I'm now at 920 points, and my "potential" savings using a similar model actually exceed $750K by 2054. The biggest underlying assumption in this type of extrapolation is that we continue to use these points at the same rate, every year, until 2054. That's our GOAL!

 

[kanerf]

Looks like I can post the link now for the google sheet to calculate a break even point with or without opportunity cost. The spreadsheet is protected but allows all users to edit the required inputs. If you would like to take a look it is available here:

DVC Break Even Calculator

Some notes about the spreadsheet:
1. Please download the file to your computer or save it to your personal drive if you want to make changes to the inputs. Anyone with the link can edit the inputs so your information will be lost if you don't.
2. If you find any errors in the calculations or if you want an unprotected version to play with let me know.
3. The opportunity cost calculation is a rough estimate as it calculates an average return on investment at the end of the year after maintenance fee cost, interest payments and savings over rack rates/rental rates are accounted for.
4. I left it up to the user to determine how they wanted to determine the cost of the stay without DVC whether that is the cash rate or point rental rate.
5. Current input values assume 3% increase per year in both the MF and rack rate for rooms although these values would be replaced by your actual information over time.

Thanks for posting this. Saves me some work. I will use this and duplicate for my different resales. I have a lot of them and tend to buy the smaller ones with the idea of easy disposal at some point, should it come to that. Also, much easier to give a small contract to my nieces and nephews (or the grands) then a large one.

 

[JPKanks]

Looks like I can post the link now for the google sheet to calculate a break even point with or without opportunity cost. The spreadsheet is protected but allows all users to edit the required inputs. If you would like to take a look it is available here:

DVC Break Even Calculator

Some notes about the spreadsheet:
1. Please download the file to your computer or save it to your personal drive if you want to make changes to the inputs. Anyone with the link can edit the inputs so your information will be lost if you don't.
2. If you find any errors in the calculations or if you want an unprotected version to play with let me know.
3. The opportunity cost calculation is a rough estimate as it calculates an average return on investment at the end of the year after maintenance fee cost, interest payments and savings over rack rates/rental rates are accounted for.
4. I left it up to the user to determine how they wanted to determine the cost of the stay without DVC whether that is the cash rate or point rental rate.
5. Current input values assume 3% increase per year in both the MF and rack rate for rooms although these values would be replaced by your actual information over time.

Thanks for doing this and sharing. I am back and forth on DVC and today is a day where I want to buy. For Column G [
Cash Price or Point Rental Price for Equivalent Room(s)] do you think it's safe to just use a rental price of $19 per point? I referenced the board sponsor and it is $19 booking seven months out, $21 eleven months out. With a 7% ROI opportunity cost, it seems it may never be worth it (i.e. For my calculations of a 130 point contract @ BW for $125 a point, I would have saved ~$19k while investing would have netted me $86k over that same timeframe). Am I looking at this wrong?
I appreciate your insight.