[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『休日以外の日数』（mino）
こんにちはです。 今、出納帳ぢゃないのですが、月々のローンの返済額を表にしています。
a b c d e f g h i j k l 1 住宅ローン マイカー NICOS DC ジャパネット 生活費 携帯 自動車保険 積立 2 TOTAL 1,550,000 450,000 100,000 170,000 140,000 30,000 22,000 9,450 20,000 収入額 残額 3 月額 50,000 18,000 16,670 15,104 7,000 30,000 22,000 9,450 20,000 285,200 4 2007/8/31 1,500,000 432,000 83,330 154,896 133,000 96,976 5 月額 50,000 36,000 15,104 7,000 30,000 22,000 9,450 20,000 223,200 6 2007/9/28 1,450,000 396,000 83,330 139,792 126,000 33,646
簡単にこのような表を作成しています。 2行目（TOTAL）に各借入先（1行目）の合計があり、月額の支払予定額（3行目）を差し引き、次の月（4行目）へとなるよう計算式をいれています。
収入額の欄が、今回ご相談したいところでして、 土日祝以外の日数を自動で返し、収入額のセルに（日数×日給額）となるようにすることはできるのでしょうか。 M1セルに、平均日給12,400とし、K3セルに=SUM(21*M$1) と日数を手作業で変更しています。 残額には、L4=K3-B3-C3-D3-E3-F3-G3-H3-I3-J3
例えば、8月の稼働日数ならば23日。9月は18日。DATE関数とかでもできますか？ 表は作り直しても構いません。 DAYS360関数を利用して稼動日数を指定してみましたが、#NAME!エラーがでてしまったので諦めました。しかも、360日とするため31日までの月も30日と返されますよね…。
月額は、固定ではありません。 月給額によっては、余裕ある月に対して多めに返済するため手入力で金額は入力しています。 目的は、返済計画を立てるため、月々の給与から各返済に振り分け、いつ頃に完済予定かが分かるように…予定通りになるか分かりませんが(-д-;) 追々、PMT関数なども取り入れ、各項目の利率・年率なども分かるように表付けしようと思っています。 お忙しいかと思いますが、ご教授よろしくお願いします。 (mino)
>収入額の欄が、今回ご相談したいところ
1. 祝日は自分でリストを作る必要があります。
下例では「N2:N30」に作ったとします。
2. A列の日付は、当月の稼動最終日以降であるものとします。 (8月なら31日のみ、9月なら28日〜30日のどれか)
理由：月末の算出プロセスを省きたい為
3. 計算式 K3セル =M$1*NETWORKDAYS(A4-DAY(A4)+1,A4,N$2:N$30)
「K3:K4」の2セルを一括選択して、フィルダウン
行 ___K___ __L___ __M___ ____N____ 1 12,400 祝日 2 収入額 残額 2007/9/17 3 285,200 2007/9/23 4 96,976 2007/9/24 5 223,200 2007/10/8 6 33,646 2007/11/3
※ Networkdays関数はアドインが必要です。 メニュー[ツール]→[アドイン]→[分析ツール]にチェック。
(半平太)
半平太さん。 ご回答ありがとうございます。 祝日リストの作成方法なのですが 一度カレンダーか何かを作成したほうがいいのでしょうか。 表の右列に、祝日リストとして カレンダーをみながら手入力で
祝日 2007/9/17 2007/9/24 2007/10/8 2007/11/23 2007/12/24 としています。 また、収入額は平日のみの計算をしています。 となると、土日もリストを作るべきなのでしょうか。 (mino)
> 祝日リストの作成方法なのですが > 一度カレンダーか何かを作成したほうがいいのでしょうか。 > 表の右列に、祝日リストとしてカレンダーをみながら手入力で 自動的に作ってくれるフリーのソフトはあります。 どのように作ったとしても、最終的にはリストの形になっていることを想定した回答です。
>土日もリストを作るべきなのでしょうか。 Networkdays関数は土日を排除するものですから、不要です。 (私の案を実際にトライしていないのですか？)
(半平太)
半平太さん。 名前を定義することで、フィルしたら祝日の日付が表示されると思ってたので…(^-^;) そっちに時間をとられてしまいましたorzすみません。
計算されてました！ありがとうございます。 ぐぐってみたりしたのですが、分からない点がもう一つあるのですが 先月の集計にすることは可能ですか？ 給与形態が、月末〆の翌月末払いとなるため A列は給料日として設定していました。 ※K列で、8/31は7月分の給与、9/28は8月分の給与とする。 =M$1*NETWORKDAYS(A4-DAY(A4)+1,A4,N$2:N$30) ~~~~ ~~~~ ~~~~ ~~~を先月に設定するしかないですよね…。 (mino)
> ~~~を先月に設定するしかないですよね…。 そんなことはありません。(半平太)
そうなると逆に、A列の日付は、当月中ならいつの日付でも構いません。
K3セルの式は ↓ に変わります。 =M$1*NETWORKDAYS(DATE(YEAR(A4),MONTH(A4)-1,1),A4-DAY(A4),N$2:N$16)
半平太さん。 できましたぁ。ありがとうございます！
度々で申し訳ないのですが、もう一ひねり加えて 返済額を入力したら、総額-(返済額-利率)=残額のようにしていきたく
a b … n m o 1 住宅ローン マイカー 利率 回数 2 TOTAL 1,550,000 450,000 住宅 12.0% 12 3 月額 50,000 18,000 マイカー 5.0% 12 4 2007/8/31 1,500,000 432,000 5 月額 50,000 36,000 6 2007/9/28 1,450,000 396,000
上記の表示ですと、5万返済したことになっている状態になっています。 私なりに… n列以降に利率を表示する欄を設けて、PV関数でやってみました。 月額の行に、返済する額をいれ、利息を引いた額をA4に表示するようにしたかったのですが 一年返済を目標として… =PV(M2/12,O2,A3) としても、正しい数値がでません。 数式が違うとは思いますが、どのような関数を使えばうまくいくのでしょうか…。 (mino)
>総額-(返済額-利率)=残額のようにしていきたく 「総額」って、どこにあるのですか？
> a b … n m o >1 住宅ローン マイカー 利率 回数 列番の順がおかしい気がするのですが、、、 (半平太)
分りました。 m(__)m 「総額」とはTOTALですね。 (半平太)
(1) B4セル =B2-(B3-INT(B2*$O$2/$P$2)) (2) C4セル =C2-(C3-INT(C2*$O$3/$P$3))
行 ____A____ ____B_____ ___C____ ____N_____ __O__ _P_ 1 住宅ローン マイカー 2 TOTAL 1,550,000 450,000 住宅ローン 12.0% 12 3 月額 50,000 18,000 マイカー 5.0% 12 4 2007/8/31 1,515,500 433,875 5 月額 50,000 36,000 6 2007/9/28 1,480,655 399,682
a b c … n o p
1 住宅ローン マイカー 利率 回数
2 TOTAL 1,550,000 450,000 住宅 12.0% 12
3 月額 50,000 18,000 マイカー 5.0% 12
4 2007/8/31 1,500,000 432,000
5 月額 50,000 36,000
6 2007/9/28 1,450,000 396,000
こうでした…。しかも、アルファベット順も微妙に違ってましたねぇ…すみません。
総額はTOTALの行(2行目)に、現在の残額を記入しています。 過去ログで似たような質問があったようなので、参考にしていますが進行できず…orz
理想では、B3に5万返済に回すとして、B2-B3(元金+利息)=B4 ※1,550,000×12.0%×31日÷365日=15,797(利息） 50,000-利息(15,797)=元金(34,203) B4=1,515,797としたい。
普通に利息を別欄に作成して、そこから引くのが手っ取り早いのでしょうか…。 (mino)
半平太さん。 理想どおりでしたぁ！ありがとうございます！！ 財務関数ばかりに気をとられてました。 (mino)
> ※1,550,000×12.0%×31日÷365日=15,797(利息）
少なくとも、住宅ローンは月利のハズですが、、、 ※1,550,000×12.0% ÷ 12 = 15,500 (円未満切捨て)
> (1) B4セル =B2-(B3-INT(B2*$O$2/$P$2)) > (2) C4セル =C2-(C3-INT(C2*$O$3/$P$3)) 上の式は正しくありませんでした。m(__)m たまたま12回払いだったので合っているだけでした。
(1) B4セル =B2-(B3-INT(B2*$O$2/12)) (2) C4セル =C2-(C3-INT(C2*$O$3/12)) です。また、残額がマイナスになった場合のことを考えた式に必要があります。 現在考慮中ですのでしばらくお待ち下さい。
(半平太)
今回の表の構成では、単にこうするしかありませんでした。(半平太) ↓ (1) B4セル =MAX(0,B2-(B3-INT(B2*$O$2/12))) (2) C4セル =MAX(0,C2-(C3-INT(C2*$O$3/12)))
※ くれぐれも、不必要な返済額を入力しないようお願いします。 (残高が4万円にまで減っているのに、返済額を50,000円も入力しないですよね？)
半平太さん。 ありがとうございます。 シュミレーションが難しいですね…。 契約上、5年(60回)となっていますが、 1年(12回)で終了させようと思い、 表中では12回としていました。 余裕があれば、5回や6回で終了させようとも思っています。
>> (残高が4万円にまで減っているのに、返済額を50,000円も入力しないですよね？) そうですね。 残高が4万だとしたら、次月の月額残高が0円となるよう月額を調整しています。 ※40,400で0になるように
利率の条件とかよく分かりませんが…。 (mino)
>契約上、5年(60回)となっていますが、 > 1年(12回)で終了させようと思い、 表中では12回としていました。 > 余裕があれば、5回や6回で終了させようとも思っています。
1.余裕があるかどうか分かるのはいつですか？ 初めから分っているとも思えないのですが、、、、
2.リボルビングなら別ですが、通常の住宅ローンだと、 「元利均等払い」が普通で、勝手に「今月は余裕があるので多目に返済します」 なんて言えないのでは？
3.また、理論的に決定される返済額でないと、シミュレーションなんて 出来ないとも思うのですが、、、、(本当に毎月の返済額を手入力するのですか？)
(半平太)
「住宅ローン」と「マイカーローン」が元利均等払いだとした場合の 返済額自動シミュレーション表
(契約期間は年数ですので注意して下さい。)
(1) B3セル =PMT($O$2/12,$P$2*12,-$B$2) (2) B4セル =MAX(0,B2-(B3-INT(B2*$O$2/12)))
(3) C3セル =PMT($O$3/12,$P$3*12,-$C$2) (4) C4セル =MAX(0,C2-(C3-INT(C2*$O$3/12)))
行 ____B_____ ___C____ ____N_____ _O__ _____P______ 1 住宅ローン マイカー 借入種別 利率 契約期間(年) 2 1,550,000 450,000 住宅ローン 4.0% 5 3 28,546 38,523 マイカー 5.0% 1 4 1,526,620 413,352 5 28,546 38,523 6 1,503,163 376,550
(半平太)
半平太さん。 確かに住宅ローンとなると、元利均等が一般的ですね。 繰上げもできなかったでしたか…調べてみます。 カードの繰上げは可能でしたよね。
>>1.余裕があるかどうか分かるのはいつですか？ 初めから分っているとも思えないのですが、、、、 あくまでも予定なのですが 月末の収入額次第ですが、まず均等に振り分けたあと残額と比較し、 住宅ローンは無理としても、他の借入先に金額を追加していこうと思っていました。 なので、初めから余裕だとはわからないのですが… 余裕だと思っても、想定外な事態もあるかも知れないですからねぇ…（-ω-;)
2.リボルビングなら別ですが、通常の住宅ローンだと、 「元利均等払い」が普通で、勝手に「今月は余裕があるので多目に返済します」 なんて言えないのでは？
お恥ずかしい話ですが、金融会社などの借入れもあり 元利均等だと利息も期間もかかってしまうために多目の返済と設定したかった次第です。
3.また、理論的に決定される返済額でないと、シミュレーションなんて 出来ないとも思うのですが、、、、(本当に毎月の返済額を手入力するのですか？)
毎月の返済額は手入力してました…。 この場合、残債額と期間によっては年率や利息が変わってきますよね。
マイカーローンや住宅ローンのように利率が低いものであれば、元利均等でもいいのですが。
混乱させてしまい、申し訳ありませんでした。 (mino)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.